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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 14:05:36  来源:igfitidea点击:

SQL query one to many relationship

sqljoin

提问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