SQL查询:列出一个表中未出现在另一表中的所有项目

本文介绍了SQL查询:列出一个表中未出现在另一表中的所有项目的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在开发一个培训跟踪程序，但现在无法确定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