SQL查询一对多关系

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.

错过“安全意识”课程的员工似乎没有出现，并且陷入了困境。

5 个解决方案

#1

use LEFT JOIN and move the filtering condition during the joining of the table (specifically in the ON clause)

使用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

SQLFiddle Demo
SQLFiddle演示

RESULT

结果

╔════╦══════╦════════════════════╗
║ ID ║ NAME ║       CLASS        ║
╠════╬══════╬════════════════════╣
║  1 ║ Bob  ║ Security Awareness ║
║  2 ║ Tom  ║ Security Awareness ║
║  3 ║ John ║ (null)             ║
╚════╩══════╩════════════════════╝

#2

You are performing an inner join, what you want is a left outer join. The difference is: an inner join will only results 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.

您正在执行内部联接，您想要的是左外部联接。不同之处在于：只有在连接表中存在匹配项时才会生成内部联接。左外连接将返回主表中的所有结果，无论连接表中是否有结果。

#3

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

如果这相当于

#4

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

#5

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

#1