select aa.fid,aa.fname,bb.sid,isnull(cc.sname,'') from
table1 aa left outer join likeTable bb on aa.fid=bb.fid
left outer join table2 cc on bb.sid=cc.sid
table1 aa left outer join likeTable bb on aa.fid=bb.fid
left outer join table2 cc on bb.sid=cc.sid
sname = isnull((select top 1 sname from secondtable c where c.sid = b.sid),'')
from firsttable a full outer join linktable b on a.fid = b.fid
OutPut:
FID FName SID SName
--- -------- ----- -------
1, 'desc1', 1, 'name1'
1, 'desc1', 3, 'name3'
2, 'desc2',
3, 'desc3', 2, 'name2'
3, 'desc3', 5, 'name5'
4, 'desc4',
5, 'desc5',
6, 'desc5', 6, 'name6'你给的答案是不是有错?
5, 'desc5',
6, 'desc5', 6, 'name6'
是怎么得出来得?
FirstTable a left outer join linktable b on a.FID=b.FID
left outer join SecondTable c on bb.sid=c.SID
insert into firsttable
select
1, 'desc1' union select
2, 'desc2' union select
3, 'desc3' union select
4, 'desc4' union select
5, 'desc5' union select
6, 'desc6'create table linktable(fid int,sid int)
insert into linktable
select 1, 1 union select
1, 3 union select
3, 2 union select
3, 5 union select
6, 6create table secondtable(sid int,sname char(10))
insert into secondtable select
1, 'name1' union select
2, 'name2' union select
3, 'name3' union select
4, 'name4' union select
5, 'name5' union select
6, 'name6' union select
7, 'name7' union select
8, 'name8'goselect a.fid,a.fname,sid=isnull(b.sid,'') ,
sname = isnull((select top 1 sname
from secondtable c where c.sid = b.sid),'')
from firsttable a full outer join linktable b on a.fid = b.fid
go
以下是得到的结果:1 desc1 1 name1
1 desc1 3 name3
2 desc2 0
3 desc3 2 name2
3 desc3 5 name5
4 desc4 0
5 desc5 0
6 desc6 6 name6