It's not a secret anymore!*
The moose likes JDBC and the fly likes matching rows from one table to another Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "matching rows from one table to another" Watch "matching rows from one table to another" New topic
Author

matching rows from one table to another

Billy Vandory
Ranch Hand

Joined: Mar 23, 2010
Posts: 57
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

Joined: Jun 21, 2008
Posts: 423
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

Joined: Mar 23, 2010
Posts: 57
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 !
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: matching rows from one table to another
 
Similar Threads
Problem with persisting entities because they become detached
Struts2 and XML subnodes to show database relationships?
Using Struts2 to xml format Hibernate objects
Why doesn't Javaranch show how many times a post has been visited?
XDoclet, JBossQL and EJB QL: Joining multiple tables (2+) possible?