Problem with Querry

[mevasquez]

I have three tables, personnel, qualification, qualifications.

The personnel table list fname,lname and other information about the person
The qualification table list all the different qualifications/training, i.e. Annual Safty Training, Divisional Coordinator, Tow Tractor, etc.
The qualifications table maintains all personnel and their qualifications. Smith, Tow Tractor; Taylor, Tow Tractor; Jones, Annual Safty Training, etc.

What I am trying to do is find all the people who are not qualified for a particular qualification.
If I choose Annual Safty Training, the result set should be, Smith and Taylor.
If I choose Divisional Coordinator, the result set should be Smith, Taylor and Jones, etc.
If I choose Tow Tractor, the result set should be Jones.

The Primary Key for Personnel is pid
The Primary Key for Qualification is qid
The Qualifications table has FK to Personnel and the Qualification table

Any help would be appreciated.

Thanks,

Mike

 

[rac2]

SELECT *
FROM Personnel
WHERE pid NOT IN (
      SELECT pid FROM Qualifications 
      WHERE qid = @target_qualification_id
     )

Everyone qualified on @target_qualification_id is in the subquery; the outer query gives everyone else.

 

[mevasquez]

Thanks, that was it. Looks like I will have to read up on the NOT IN statement. Never used it before.

Thanks again,

Mike