Find a record that doesn't exist

[tmarsh]

I have a training database with multiple courses. Certain staff do course1 and thereafter course2. Course1 is only done once and course2 is a refresher done annually. I want the query to find staff that have done course2 but not course1.

Thanks.

 

[FoFa]

Sure, how many tables are to be joined?
I would guess staff is in one table and the courses they took in another?
What data element is common between all the tables?

 

[tmarsh]

Sure, how many tables are to be joined?
I would guess staff is in one table and the courses they took in another?
What data element is common between all the tables?

Yes, tb_staff is linked to tb_courses_done. tb_staff has staff_id to a composite key in tb_courses_done using courses_done_id, staff_id and the start date of each course.

 

[FoFa]

Try something like this:
Select {Whatever columns you want}
From tb_staff S
inner join tb_courses_done CD1 on S.staff_id = CD1.staff_id and CD1.course = 'Course2'
left join tb_courses_done CD2 on S.staff_id = CD2.staff_id and CD2.course = 'Course1
Where CD2.staff_id is null