SQL查询:列出一个表中未出现在另一表中的所有项目 [英] SQL query: list all items in one table that do not appear in another table

问题描述

我正在开发一个培训跟踪程序，但现在无法确定SQL查询.

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

我有3张桌子:employees, trainingRecords, masterList.

employees和trainingRecords通过empID fkey关联.

employees and trainingRecords are related through the empID fkey.

trainingRecords和masterList通过TID 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).

我想用trainingRecords表中未说明的masterList中的所有项目填充一个列表框.

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

由于trainingRecords表为空白，因此它应该从employees表返回lName, fName，并且对于主列表中的所有条目都返回docName, docNumber.

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?

推荐答案

我假设您要多次显示所有员工及其尚未完成的培训文档.

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

示例结果:

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

这篇关于SQL查询:列出一个表中未出现在另一表中的所有项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！