SQL SQL查询一对多关系
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15251761/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query one to many relationship
提问by Rich
I have a table for Employees and another table with Training. The training table contains various training classes that the employees have completed. We have mandatory security awareness training, so every employee must complete this training class. I'm having trouble running a query that will return ALL employees' either listed completing the training or not.
我有一张员工表和另一张培训表。培训表包含员工已完成的各种培训课程。我们有强制性的安全意识培训，所以每个员工都必须完成这个培训课程。我在运行查询时遇到问题，该查询将返回列出的所有员工是否完成培训。
Example Employee table
示例员工表
╔════╦══════╗
║ ID ║ NAME ║
╠════╬══════╣
║ 1 ║ Bob ║
║ 2 ║ Tom ║
║ 3 ║ John ║
╚════╩══════╩
Example Training table
示例训练表
╔════╦══════════════╦════════════════════╗
║ ID ║ DEPARTMENT_ID║ CLASS ║
╠════╬══════════════╬════════════════════╣
║ 1 ║ 1 ║ Security Awareness ║
║ 2 ║ 1 ║ Workplace Safety ║
║ 3 ║ 2 ║ Security Awareness ║
╚════╩══════════════╩════════════════════╝
Target result
目标结果
╔════╦══════╦════════════════════╗
║ ID ║ NAME ║ CLASS ║
╠════╬══════╬════════════════════╣
║ 1 ║ Bob ║ Security Awareness ║
║ 2 ║ Tom ║ Security Awareness ║
║ 3 ║ John ║ (null) ║
╚════╩══════╩════════════════════╝
The query that I am using is
我正在使用的查询是
SELECT employee.id, employee.name, training.class
FROM employee
JOIN training ON employee.id = training.department_id
WHERE training.class LIKE '%SECURITY%'
ORDER BY employee_id
The employee missing the "Security Awareness" class just don't appear, and falls through the cracks.
错过“安全意识”课程的员工没有出现，并从裂缝中掉了下来。
回答by John Woo
use LEFT JOINand move the filtering condition during the joining of the table (specifically in the ONclause)
LEFT JOIN在加入表时使用和移动过滤条件（特别是在ON子句中）
Another concern is use single quotes: ' 'not ‘ '
另一个问题是使用单引号：' '不‘ '
SELECT employee.id,
employee.name, training.class
FROM employee
LEFT JOIN training
ON employee.id = training.department_id AND
training.class LIKE '%SECURITY%'
ORDER BY employee.id
RESULT
结果
╔════╦══════╦════════════════════╗
║ ID ║ NAME ║ CLASS ║
╠════╬══════╬════════════════════╣
║ 1 ║ Bob ║ Security Awareness ║
║ 2 ║ Tom ║ Security Awareness ║
║ 3 ║ John ║ (null) ║
╚════╩══════╩════════════════════╝
回答by GojiraDeMonstah
You are performing an inner join, what you want is a left outer join. The difference is: an inner join will onlyresults where there is a match in the joined table. A left outer join will return all results from the primary table, whether there are results in the joined table or not.
您正在执行内连接，您想要的是左外连接。不同之处在于：内部联接只会在联接表中存在匹配的情况下产生结果。左外连接将返回主表中的所有结果，无论连接表中是否有结果。
回答by Melanie
Instead of JOIN use LEFT OUTER JOIN. I'd also change your WHERE clause to
而不是 JOIN 使用 LEFT OUTER JOIN。我也会将您的 WHERE 子句更改为
WHERE training.Id = 1
if that's equivalent
如果那是等价的
回答by Jeff70227
What you are looking for is called an Outer Join. In this case you will need a left outer join:
您正在寻找的称为外部联接。在这种情况下，您将需要一个左外连接：
SELECT employee.id, employee.name, training.class
FROM employee LEFT OUTER JOIN training ON employee.id = training.department_id
WHERE training.class LIKE '%Security%'
ORDER BY employee_id
回答by Usman YousafZai
Select e.id, e.name, t.class from employee e left outer join training t on e.id = t.department_id where t.class like '%Security%' order by e.id
