Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

matching rows from one table to another

 
Billy Vandory
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

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


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


If your database does not support nested subqueries, you must write 3 separate queries
and store intermediate results in temporary tables.
 
Billy Vandory
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ireneusz Kordal wrote:Hi,

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


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


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 !
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic