sql join返回不需要的结果 [英] sql join return unwanted result

问题描述

我有两张桌子



部门

< img src =http://www9.0zz0.com/2015/ 02/14/00/463246826.png/>



教练



< img src =http://www9.0zz0.com/2015/02/14/00/386938495.png/>



i希望归还部门及其经理



有些部门没有经理，所以经理姓名应为空



但结果显示购物车产品< br $>


结果

i have two tables

Department
<img src="http://www9.0zz0.com/2015/02/14/00/463246826.png"/>

Instructor

<img src="http://www9.0zz0.com/2015/02/14/00/386938495.png"/>

i want to return department and it's manager

some departments have not managers so manager name should be null

but the result show cartisan product

result

select  dpt.Dept_Id , dpt.Dept_Name,dpt.Dept_Location, inst.Ins_Name as [Dept_Manager] ,dpt.Dept_Desc
 from  Department dpt  join Instructor as inst
     on (dpt.Dept_Id=3) AND ((dpt.Dept_Manager = inst.Ins_Id) OR (dpt.Dept_Manager is null))

< img src =http：//www9.0zz0。 com / 2015/02/14/00/2166699126.png/>



结果应该是



Dept_Id Dept_Name Dept_Location Dept_Manager Dept_Desc

3 tes ma NULL NULL



所以我的代码有什么问题

<img src="http://www9.0zz0.com/2015/02/14/00/216699126.png"/>

result should be

Dept_Id Dept_Name Dept_Location Dept_Manager Dept_Desc
3 tes ma NULL NULL

so what is wrong with my code

推荐答案

只需按照评论中的说明进行LEFT JOIN。

Just do a LEFT JOIN as mentioned in the comments.

SELECT *
FROM Department d
LEFT JOIN Instructor i ON d.dept_manager = i.ins_id
WHERE d.dept_id = 3

这篇关于sql join返回不需要的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！