• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Problem self joining

 
Mike Nightsky
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Maximilian Xavier Stocker
Ranch Hand
Posts: 381
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic