Select statement that gives me the instructors who have not taken a specific training

Question

I have 4 tables...

tFaculty - FacultyID, FirstName, LastName, UserName, DepartmentID

tDepartment - DepartmentID, Department

tTraining - TrainingID, Training

tIntermediate - IntermediateID, FacultyID, TrainingID, DateCompleted

What I need is all of the faculty members who have not taken TrainingID 1. My issue is the SELECT statement I've come up with gives me only the faculty in the Intermediate table, but nobody else. For example, if Ed has taken TrainingID 1 but Susan has not, Ed will show up in the results, but not Susan even though Ed has taken TrainingID 1. My SELECT statement is this:

SELECT LastName AS LS
FROM tFaculty AS F LEFT JOIN tIntermediate AS I ON F.FacultyID = I.FacultyID
WHERE I.TrainingID <> 1;

What am I doing wrong?

I also tried this:

SELECT LastName AS LS
FROM tFaculty AS F LEFT JOIN 
     (SELECT I.*
      FROM tIntermediate AS I
      WHERE I.TrainingID <> 1
     ) I
     ON F.FacultyID = I.FacultyID;

It now gives me all the instructors, but it still includes those who have taken TrainingID 1

Answer 1

Or easier to read, and performance just fine (unless you have millions of instructors 🤪):

SELECT LastName AS LS
FROM tFaculty F
WHERE NOT EXISTS (
  SELECT *
  FROM tIntermediate I
  WHERE F.FacultyID = I.FacultyID
  AND I.TrainingID = 1
)

This style is close to a direct transliteration from the English requirement to SQL.