Selecting records based on course prerequisites 1

[meumax]

I have two tables. One containing records of training qualifications staff have received with a structure like:

StaffID | QualiicationID
1 | 1
1 | 2
1 | 26
2 | 1
2 | 2
3 | 1

And another showing the qualifications required to be eligible for a certain position with a structure like:

Position | fkQualificationID
boss | 1
boss | 2
boss | 26

So, to be eligible for the "boss" position, an employee would need qualifications 1,2 and 26 which would mean only employee "1" would be eligible.

Given a position, how can I select all staff from the staff qualifications table which have all the necessary qualifications for the position?

 

[r937]

[tt]select staffid
from qualifications Q
inner
join staff S
on Q.fkQualificationID = S.QualificationID
where Position = 'boss'
group
by staffid
having count(*)
= ( select count(*)
from qualifications
where Position = 'boss' )[/tt]

rudy

http://r937.com/