wood burning stoves
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
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "SQL Problem self joining" Watch "SQL Problem self joining" New topic

SQL Problem self joining

Mike Nightsky
Ranch Hand

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

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


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
I agree. Here's the link: http://aspose.com/file-tools
subject: SQL Problem self joining
It's not a secret anymore!