CODEWITHSUNDEEP
sqlms-access
JC3
JC3

Reputation: 79

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

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

Upvotes: 1

Views: 61

Answers (2)

Bohemian
Bohemian

Reputation: 425033

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269823

The LEFT JOIN is being turned into an INNER JOIN by the WHERE. In most databases, you would just move the condition to the ON clause. But, MS Access doesn't support this.

Instead, use a subquery to filter before the JOIN:

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;

Upvotes: 2

Related Questions