matching rows from one table to another

[Billy Vandory]

Hi,

I have two views:

JOB
------------
jobId, SkillId

and

PERSON
-----------
personId, SkillId


The job view returns:
1, 1
1, 2

The person view returns
1, 1
2, 1
3, 1
3, 2

I'm trying to write a query where it returns the person that has all the skills for the job. So the job view only has one job which requires skill 1 and 2.
The only person that matches that criteria is personid 3


I thought of

select *
from person p, job j
where p.jobid = j.jobid

but it returns all the people because all the people match on at least one skill..

I was thinking I may need to count the skills and count how many skills the person matches on and do it that way, but is there a simpler way?

Thanks

Billy

[Ireneusz Kordal]

Hi,

if your database supports set operators, you could do it using MINUS:

SELECT DISTINCT personid FROM PERSON P
WHERE
  NOT EXISTS (
     SELECT SKILLID FROM JOB
     WHERE JOBID = 1 -- desired job
     MINUS
     SELECT SKILLID FROM PERSON P1
     WHERE P.personid = p1.personid
  )

if your database does not support set operators, you need to use two correlated subqueries
(but db must support nested subqueries)

SELECT DISTINCT personid FROM PERSON P
WHERE
  NOT EXISTS (
     SELECT SKILLID FROM JOB J
     WHERE JOBID = 1 -- desired job 
          AND NOT EXISTS (
              SELECT JOBID 
              FROM PERSON P1
              WHERE P.PERSONID = P1.PERSONID
                  AND j.skillid = p1.skillid
          )
  )

If your database does not support nested subqueries, you must write 3 separate queries
and store intermediate results in temporary tables.

[Billy Vandory]

Ireneusz Kordal wrote:Hi,

if your database supports set operators, you could do it using MINUS:

SELECT DISTINCT personid FROM PERSON P
WHERE
  NOT EXISTS (
     SELECT SKILLID FROM JOB
     WHERE JOBID = 1 -- desired job
     MINUS
     SELECT SKILLID FROM PERSON P1
     WHERE P.personid = p1.personid
  )

if your database does not support set operators, you need to use two correlated subqueries
(but db must support nested subqueries)

SELECT DISTINCT personid FROM PERSON P
WHERE
  NOT EXISTS (
     SELECT SKILLID FROM JOB J
     WHERE JOBID = 1 -- desired job 
          AND NOT EXISTS (
              SELECT JOBID 
              FROM PERSON P1
              WHERE P.PERSONID = P1.PERSONID
                  AND j.skillid = p1.skillid
          )
  )

If your database does not support nested subqueries, you must write 3 separate queries
and store intermediate results in temporary tables.

thanks so much for that answer.. i tried it and it works.. now all i have to do is chew on it and try and understand why...

thanks again !