Oracle SQL Subquery -- match multiple rows

user_13803215 0
Viewed 93

I have an Oracle (PeopleSoft) table with Employee IDs and job responsibilities the employees are able to perform.

id | job
------------
01 | JobA
01 | JobB
01 | JobC
02 | JobA
02 | JobC
03 | JobA
03 | JobC
03 | JobF
04 | JobH
04 | JobC
05 | JobA
05 | JobC

Only there's about 1000 unique employees in this list

Using SQL, how can I find an employee who has the EXACT same skill set as Employee 02?

Employee 02 can do JobA and JobC - the SQL should only return employee 05 since they can also do just JobA and JobC. Employee 03 has extra skills (JobF) so they shouldn't be included in the results.

I assume I'll need a subquery to get the list of jobs I want ... something like

Select job 
From table where id = '02'

But I'm not sure how to compare each unique Employee ID to that list and get correct results.

Any guidance is appreciated. Thanks for your help.

peoplesoft subquery oracle-sqldeveloper oracle sql
1 Answers

You can use the LISTAGG and CTE as follows:

WITH CTE AS 
(SELECT ID, JOB,
        LISTAGG(JOB, ',') WITHIN GROUP (ORDER BY JOB) OVER (PARTITION BY ID) JOBS
   FROM YOUR_TABLE)
SELECT C1.ID, C1.JOB 
  FROM CTE C1 JOIN CTE C2
    ON C1.JOBS = C2.JOBS
 WHERE C1.ID <> '02' AND C2.ID = '02';

Or you can use the GROUP BY and HAVING as follows:

SELECT C1.ID
  FROM CTE C1
 WHERE C1.ID <> '02'
GROUP BY C1.ID
HAVING LISTAGG(C1.JOB, ',') WITHIN GROUP (ORDER BY C1.JOB) =
(SELECT LISTAGG(C2.JOB, ',') WITHIN GROUP (ORDER BY C2.JOB)
   FROM CTE C2
  WHERE C2.ID = '02');
user_115656290
Related