SQL query: list all items in one table that do not appear in another table

Question

I'm working on a training tracker program and I'm at a point where I can't figure out the SQL query.

I have 3 tables: employees, trainingRecords, masterList.

employees and trainingRecords are related through the empID fkey.

trainingRecords and masterList are related through the TID fkey.

Right now the training records table is blank because nothing has been entered (all employees have no training).

I want to populate a listbox with all of the items in the masterList that are unaccounted for in the trainingRecords table.

Since the trainingRecords table is blank, it should be returning lName, fName from the employees table and docName, docNumber for all entries in the master list.

I'm stumped. Any suggestions?

Answer 1

I'm assuming you want to display all employees multiple times with the training documents they have not done yet.

SELECT a.lName, a.fName, b.docNumber, b.docName 
FROM
(SELECT e.lName, e.fName, t.TID 
 FROM employees e
 LEFT JOIN trainingRecords t ON e.empID = t.empID
) AS a,
(SELECT m.docNumber, m.docName, t.TID
 FROM masterList m
 LEFT JOIN trainingRecords t ON m.TID = t.TID
) AS b
WHERE a.TID IS NULL OR b.TID IS NULL
ORDER BY a.lName, b.docNumber

example results:

lName     fName  docNumber          docName
Simpson   Homer     1      Nuclear Physics for Dummies
Simpson   Homer     2      Nuclear Physics for Beginners
Simpson   Homer     3      Advanced Nuclear Physics
Simpson   Lisa      3      Advanced Nuclear Physics

Answer 2

You want LEFT JOIN, on the left side of the join will be the table that you know will contain everything and on the right will be what you are testing against.

select masterList.* from masterList LEFT JOIN trainingRecords ON(masterList.TID = trainingRecords.TID) WHERE trainingRecords.TID IS NULL;