How do I select all id's that are present in one table but not in another

Cache Staheli Published at Dev

Cache Staheli

I am trying to get a list of department Ids are present in one table, (PS_Y_FORM_HIRE), but which don't exist in another table (PS_DEPARTMENT_VW).

Here is the basics of what I have which isn't working:

SELECT h.DEPTID FROM PS_Y_FORM_HIRE h, PS_DEPARTMENT_VW d WHERE NOT EXISTS (
    SELECT d1.DEPTID FROM PS_DEPARTMENT_VW d1 WHERE d1.DEPTID = h.DEPTID 
        and d1.SETID_GL_DEPT = 'IDBYU'
);

I'm trying to form this query in SQL Developer, but it just returns a long list of blanks (after spinning/running the query for a very long time).

In addition, I need this to be effective dated, so that it only grabs the correct effective-dated row, but I was unsure how and where to incorporate this into the query.

EDIT I neglected to mention that only the department table is effective dated. The form hire table is not. I need to get the current effectively dated row from that in this query (to make sure the data is accurate).

Also note that DEPTID isn't a key on PS_Y_FORM_HIRE, but is on PS_DEPARTMENT_VW. (Along with SETID_GL_DEPT and EFFDT).

So again, ideally, I will have a list of all the department ids that appear in PS_Y_FORM_HIRE, but which are not in PS_DEPARTMENT_VW.

MT0

SELECT DEPTID
FROM   PS_Y_FORM_HIRE
MINUS
SELECT DEPTID
FROM   PS_DEPARTMENT_VW
WHERE  SETID_GL_DEPT = 'IDBYU';

SELECT DEPTID
FROM   PS_Y_FORM_HIRE
WHERE  DEPTID NOT IN (
  SELECT DEPTID
  FROM   PS_DEPARTMENT_VW
  WHERE  SETID_GL_DEPT = 'IDBYU'
)

SELECT DEPTID
FROM   PS_Y_FORM_HIRE h
WHERE  NOT EXISTS (
  SELECT 1
  FROM   PS_DEPARTMENT_VW d
  WHERE  SETID_GL_DEPT = 'IDBYU'
  AND    d.DEPTID = h.DEPTID
)

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-03-3

Comments

0 comments

From Dev

How to select all records from one table that do not exist in another table but return NULL in the record that do not exist

From Dev

In standard SQL, how do I select rows such that for each unique value in one column, all of the values in another column are a specified value?

From Dev

In standard SQL, how do I select rows such that for each unique value in one column, all of the values in another column are a specified value?

From Dev

Maya, PYTHON: how do i select all but one in a list?

From Dev

How do I query for a set of all unique values present in my table?

Related Related

Article