救急：Sql查询并集

[99028]

select a.DepName,b.ToName from Depart a left outer join InfoTo b on a.DepId=b.DepId where a.DepId in (11,13,14,21) and b.InfoId=14


a有4条记录（a.DepId in (11,13,14,21)）：
a1
a2
a3
a4

b有2条记录（b.InfoId=14）:
b1
b2

以上查询出来的是：
DepName,ToName
------------------
a1 b1
a4 b2

结果并起来，我想得到的是
DepName,ToName
------------------
a1 b1
a2 (空)
a3 (空)
a4 b2

那么Sql如何写？

[AcHerat]

select a.DepName,b.ToName 
from Depart a left outer join InfoTo b on a.DepId=b.DepId and a.DepId in (11,13,14,21) and b.InfoId=14

[--小F--]

select
 a.DepName,isnull(b.ToName ,'')
from
 Depart a 
full  join 
 InfoTo b 
on
 a.DepId=b.DepId 
where
 a.DepId in (11,13,14,21) and b.InfoId=14

[dawugui]

select isnull(a.DepName,b.DepName) DepName , isnull(a.ToName,b.ToName) ToName
from a full join b
on a.DepName = b.DepName

[中国风]

select a.DepName,b.ToName 
from Depart a 
left outer join InfoTo b on a.DepId=b.DepId and b.InfoId=14
where a.DepId in (11,13,14,21) 

and b.InfoId=14--換一下位置

[99028]

[Quote=引用 1 楼 acherat 的回复:]
SQL code

select a.DepName,b.ToName
from Depart a left outer join InfoTo b on a.DepId=b.DepId and a.DepId in (11,13,14,21) and b.InfoId=14
[/Quote]
---------------------
这个把条件外的记录都罗列出来了，不是a.DepId in (11,13,14,21) and b.InfoId=14

[99028]

[Quote=引用 2 楼 fredrickhu 的回复:]
SQL code
select
a.DepName,isnull(b.ToName ,'')
from
Depart a
full join
InfoTo b
on
a.DepId=b.DepId
where
a.DepId in (11,13,14,21) and b.InfoId=14
[/Quote]
------------
这个依然是：
DepName,ToName
------------------
a1 b1
a4 b2

[dawugui]

and a.DepId in (11,13,14,21) and b.InfoId=14
-->
where a.DepId in (11,13,14,21) and b.InfoId=14

[-晴天]

select a.DepName,b.ToName from Depart a left outer join InfoTo b on a.DepId=b.DepId and a.DepId in (11,13,14,21) and b.InfoId=14

[99028]

[Quote=引用 3 楼 dawugui 的回复:]
SQL code
select isnull(a.DepName,b.DepName) DepName , isnull(a.ToName,b.ToName) ToName
from a full join b
on a.DepName = b.DepName
[/Quote]

----------
出错的

[99028]

[Quote=引用 4 楼 roy_88 的回复:]
SQL code
select a.DepName,b.ToName
from Depart a
left outer join InfoTo b on a.DepId=b.DepId and b.InfoId=14
where a.DepId in (11,13,14,21)


and b.InfoId=14--換一下位置
[/Quote]

--------
这个正解！谢谢

[-晴天]

create table depart(depid int,depname nvarchar(10))
insert into depart select 11,'aa' union all select 13,'bb' union all select 14,'cc' union all select 21,'dd'
create table infoto(infoid int,depid int,toname nvarchar(10))
insert into infoto select 14,11,'aaaaa' union all select 14,21,'ddddd'
go
select a.DepName,b.ToName from Depart a left outer join InfoTo b on a.DepId=b.DepId and a.DepId in (11,13,14,21) and b.InfoId=14
/*
DepName    ToName
---------- ----------
aa         aaaaa
bb         NULL
cc         NULL
dd         ddddd

(4 行受影响)
*/
go
drop table depart,infoto