SQL Problem self joining

[Mike Nightsky]

I have a table
assessment
with columns

    assessment_id
    employee_id
    skill_id
    .
    .
    .

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

mike

[Maximilian Xavier Stocker]

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.

SELECT DISTINCT employee_id FROM assessment [AS] tablea INNER JOIN assessment [AS] tableb on tablea.employee_id = tableb.employee_id WHERE tablea.skill_id=? AND tableb.skill_id=?

[Mike Nightsky]

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