Query to show records who haven't had paticular training 3

[hsingh1981]

Hi all i have query which shows all staff who had training using the course id. How do i find staff who haven't had training on a paticular courseid...such as course id = 5?


my current sql statement is:

SELECT dbo_Staff.Firstname, dbo_Staff.Surname, dbo_T_MandatoryTraining.TrainingDate, dbo_T_MandatoryTraining.CourseID
FROM dbo_Staff LEFT JOIN dbo_T_MandatoryTraining ON dbo_Staff.StaffID = dbo_T_MandatoryTraining.StaffID;

many thanks

 

[PWise]

try

SELECT dbo_Staff.Firstname, dbo_Staff.Surname, dbo_T_MandatoryTraining.TrainingDate, dbo_T_MandatoryTraining.CourseID
FROM dbo_Staff LEFT JOIN dbo_T_MandatoryTraining ON dbo_Staff.StaffID = dbo_T_MandatoryTraining.StaffID
and course id = 5
where dbo_T_MandatoryTraining.StaffID is null

 

[Remou]

How about:

SELECT dbo_Staff.Firstname, dbo_Staff.Surname
FROM dbo_Staff LEFT JOIN dbo_T_MandatoryTraining ON dbo_Staff.StaffID = dbo_T_MandatoryTraining.StaffID
WHERE dbo_T_MandatoryTraining.StaffID Is Null

 

[PHV]

SELECT S.Firstname, S.Surname
FROM dbo_Staff S LEFT JOIN (
SELECT StaffID FROM dbo_T_MandatoryTraining WHERE CourseID=5
) T ON S.StaffID = T.StaffID
WHERE T.StaffID Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hsingh1981]

Thanks guys....really appreciate you're help!