List all employees missing a specific record from another table (1 Viewer)

[smally]

Hi, I have a database which keeps records of employee's training they have done. It also keeps a record of which training is required.

In my employee table is a field which is a yes/no called IsLGVDriver. This is to determine whether the employee is a truck driver or not. (LGV = Large Goods Vehicle)

I have a table to store Training course categories, there is 16 in there at the moment. 1 of which is related to truck driving as it to do with safety and fuel efficient driving (ID = 6). A course category can have many employees, and an employee can have many course categories (ie required to be trained).
Because of the many-to-many I've got a bridge table called CourseRequirements just containing employee ID and category ID.

I have a query setup to find all employees that are marked as non-LGV drivers, but the employee requires the safety and fuel efficient driving. This is to identify which employees have wrongly been entered to need the training because they're not a truck driver:

SELECT tblEmployees.Firstname, tblEmployees.Surname, tblCourseRequirements.CategoryID, tblEmployees.IsLGVDriver
FROM tblEmployees INNER JOIN tblCourseRequirements ON tblEmployees.EmployeeID = tblCourseRequirements.EmployeeID
WHERE (((tblCourseRequirements.CategoryID)=6) AND ((tblEmployees.IsLGVDriver)=False));

What I need now is the opposite of this. To find all truck drivers that are missing the safety and fuel efficient driving requirement from the CourseRequirements table. But I don't quite know how to do this.

 

[Ranman256]

You want an OUTER JOIN.
Create a query that pulls the data you want. In the join to training, dbl-click the join and set it All items in persons, but missing in training.
Set the training.class criteria = null.

This will pull the missing people.

 

[CJ_London]

not sure that would work - the query will only return those employees without any matching records in tblCourseRequirements

I would suggest something like

SELECT E.*
FROM tblEmployees E LEFT JOIN (SELECT * FROM tblCourseRequirements WHERE CategoryID=6) CR ON E.EmployeeID =CR.EmployeeID
WHERE E.IsLGVDriver=False AND CR.EmployeeID is null

 

[smally]

Thanks CJ, that works. I always stumble when it comes to using sub-queries.

I changed IsLGVDriver to true for it to get list what I wanted (active drivers that are missing the category 6 requirement).

 

[CJ_London]

happy to help