CODEWITHSUNDEEP
sqloracle-database
Paddyngton
Paddyngton

Reputation: 55

Oracle SQL - finding sets that contain another set

Say I have a table A that contains a list of a potential employees ID's and their professional skills in the form of a skill code:

ID  | skill code
005   12
005   3
007   42
007   8
013   6
013   22
013   18

And I have another table B that lists several job position ID's and their corresponding required skill ID's:

Job ID | skill code
1        3
1        32
1        21
1        44
2        15
2        62
.
.
.

How can I find out which Job Id's a specific person is qualified for? I need to select all Job Id's that contain all the person's skills. Say for instance I need to find all job ID's that employee ID 003 is qualified for, how would I structure an Oracle SQL query to get this information?

I want to be able to enter any employee ID in a WHERE clause to find what jobs that person is qualified for.

Upvotes: 1

Views: 135

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

An idea would be to count the number of skills for every person and job:

SELECT A.id as person_id,
       B.JOB_ID
FROM A
JOIN B
ON A.skill_code=B.skill_code
GROUP BY a.id, b.job_id
HAVING count(*) = (select count(*) from b b2 where b2.job_id = b.job_id);

Not tested and assuming that tables are well normalized.

UPDATE after the OP's comment. It is asked for all the jobs which necessitate all skills of a person:

SELECT A.id as person_id,
       B.JOB_ID
FROM A
JOIN B
ON A.skill_code=B.skill_code
GROUP BY a.id, b.job_id
HAVING count(*) = (select count(*) from a a2 where a2.job_id = b.job_id);

Update2: The question was updated with:

I want to be able to enter any employee ID in a WHERE clause to find what jobs that person is qualified for.

For this, you just add WHERE a.id = :emp_id to the first query. (above group by)

Upvotes: 1

Tatiana
Tatiana

Reputation: 1499

Try this one

WITH b1 AS
  (SELECT job_id,
    skill,
    COUNT(*) over (partition BY job_id order by job_id) rr
  FROM b
  ) ,
  res1 AS
  (SELECT a.id,
    b1.job_id,
    rr,
    COUNT(*) over (partition BY id, job_id order by id) rr2
  FROM A
  JOIN B1
  ON A.skill=B1.skill
  )
SELECT id, job_id FROM res1 WHERE rr=rr2

Upvotes: 0

Related Questions