• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

matching rows from one table to another

 
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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


 
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 !
 
What's that smell? I think this tiny ad may have stepped in something.
Free, earth friendly heat - from the CodeRanch trailboss
https://www.kickstarter.com/projects/paulwheaton/free-heat
reply
    Bookmark Topic Watch Topic
  • New Topic