[SOLVED] Oracle SQL Subquery -- match multiple rows

Oracle SQL Subquery -- match multiple rows

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.

Solution

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');