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
Joined: Sep 20, 2005
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.
Joined: Aug 18, 2001
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