This week's book giveaway is in the Android forum.
We're giving away four copies of Head First Android and have Dawn & David Griffiths on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes SQL Problem self joining Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Head First Android this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL Problem self joining" Watch "SQL Problem self joining" New topic
Author

SQL Problem self joining

Mike Nightsky
Ranch Hand

Joined: Aug 18, 2001
Posts: 48
I have a table
assessment
with columns

and i want to have all employee_id's (distinct)
that have all given skill_id's

mike


Win the opportunity to make money on the Internet<br /><a href="http://sweeps.sitesell.com/minirich.html" target="_blank" rel="nofollow">http://sweeps.sitesell.com/minirich.html</a>
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
Well yes this can be done, however as the number of skills you are cross referencing increases than the number of joins increases.. this does not make for very dymanic stuff and you will also tend to see some severe performance degrading after a few joins.

Mike Nightsky
Ranch Hand

Joined: Aug 18, 2001
Posts: 48
Thanx for the answer. But i did it another way.
selected all employee_id's and counted the entrys where skill in (skill1, skill2) grouped by employee_id in an subselect, then i checked the number of skills per employee id and return only the valid counts.

select distinct employee_id form table a , (select distinct assessment_id, count(assessment_id) as counter from table where skill_id in (1,2,3,4) group by assessment_id) b where b.counter =4 and b.assessment_id = a.assessment_id

and that's the magic. a little ugly but it works for any number of skills
 
Don't get me started about those stupid light bulbs.
 
subject: SQL Problem self joining
 
jQuery in Action, 3rd edition