CODEWITHSUNDEEP
sqloracle-databasepeoplesoft
Cache Staheli
Cache Staheli

Reputation: 3673

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

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.

Upvotes: 2

Views: 2375

Answers (4)

Darryls99
Darryls99

Reputation: 931

Your question is a bit unclear around why you want effective dated rows as you are not checking effective status or any other field that may have changed between effective rows. If your question is, You want to know all DEPTIDs in PS_Y_FORM_HIRE that either don't exist or are inactive as of a current effective date, then the SQL below should help

SELECT DEPTID 
FROM PS_Y_FORM_HIRE h
WHERE 
H.DEPTID NOT IN ( SELECT d.DEPTID 
                  FROM PS_DEPARTMENT_VW d
                  WHERE d.EFF_STATUS = 'A'
                  AND d.EFFDT = (SELECT MAX(EFFDT)
                                 FROM PS_DEPARTMENT_VW d2
                                 WHERE d2.SETID_GL_DEPT = d.SETID_GL_DEPT
                                 AND d2.DEPTID = d.DEPTID
                                 AND d2.EFFDT <= CURRENT_DATE)  
                )

Upvotes: 0

sheppe
sheppe

Reputation: 718

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

Upvotes: 1

MT0
MT0

Reputation: 168351

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

or

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

or

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
)

Upvotes: 4

user5683823
user5683823

Reputation:

This seems like a job for the MINUS operation. Something like

select deptid from ps_y_form_hire where eff_date = <whatever>
minus
select deptid from ps_department_vw <where eff_date = ...>

You didn't provide information to determine what exactly you want done with the effective dates; adapt as needed.

Upvotes: 1

Related Questions