CODEWITHSUNDEEP
sqloracle-databasesubqueryoracle-sqldeveloperpeoplesoft
Chris Bombeck
Chris Bombeck

Reputation: 23

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.

Upvotes: 2

Views: 235

Answers (1)

Popeye
Popeye

Reputation: 35900

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

Upvotes: 2

Related Questions