我要连接3张表
team
id name
1 一队
2 二队people
id name teamid dutyid
duty
id name
1 队长
我的sql语句是select a.name, b.name as pname, c.name as dutyname from team as a left join people as b on a.id=b.teamid left join duty as c on b.dutyid=c.id where a.name="1" and c.name='队长'由于people表没有数据
所以我的sql语句查不出数据
我想要的结果是如果people没有数据的时候如下:
name pname dutyname
一队 null null
同时如果people有数据的时候如下:
name pname dutyname
一队 李 队长
有方法用一条sql语句实现吗?
team
id name
1 一队
2 二队people
id name teamid dutyid
duty
id name
1 队长
我的sql语句是select a.name, b.name as pname, c.name as dutyname from team as a left join people as b on a.id=b.teamid left join duty as c on b.dutyid=c.id where a.name="1" and c.name='队长'由于people表没有数据
所以我的sql语句查不出数据
我想要的结果是如果people没有数据的时候如下:
name pname dutyname
一队 null null
同时如果people有数据的时候如下:
name pname dutyname
一队 李 队长
有方法用一条sql语句实现吗?
CREATE TABLE #team(id INT, [name] NVARCHAR(100))
INSERT #team
SELECT 1, N'一队' UNION ALL
SELECT 2, N'二队'
CREATE TABLE #people(id INT, [name] NVARCHAR(100), teamid INT, dutyid INT)
--INSERT #people
--SELECT 1, N'李', 1, 1 UNION ALL
--SELECT 1, N'李', 1, 2TRUNCATE TABLE #people
CREATE TABLE #duty(id INT, [name] NVARCHAR(100))
INSERT #duty
SELECT 1, N'队长'
SELECT a.name ,
b.name AS pname ,
c.name AS dutyname
FROM #team AS a
LEFT JOIN #people AS b ON a.id = b.teamid
LEFT JOIN #duty AS c ON b.dutyid = c.id
WHERE a.[name] = N'一队' --这里的where是结果集查询出来后再过滤
AND (c.name IS NULL OR c.name = N'队长') --只是因为与NULL值比较后的结果为NULL。所以显示0条记录
from team as a left join people as b on a.id=b.teamid left join duty as c on b.dutyid=c.id and c.name='队长'
where a.name='一队'