会者不难,这样的SQL语句怎么写?

[mint]

现在有如下的表:

tb_item
ItemID userID targetID other
1 1 2 aaab
2 2 1 bbc
3 null null kkkk

表tb_user

UserID Name
1 SSS
2 MMM

需要通过SQL语句显示如下内容:
ItemID userName targetName other
1 SSS MMM aaab
2 MMM SSS bbc
3 null null kkkk
应当如何做?我主要是不懂第三记录当外键为空时如何解决?

[ych3000]

顶 chyliu(随意行走） 的语句

[marco08]

用左外連嘛

[hhjwyy]

左连了！！！

[wym840713]

targetName 哪里的?

[WangZWang]

select a.ItemID,b.Name as userName,c.Name as targetName,a.other
from tb_item as left join tb_user as b on a.UserID=b.UserID
left join tb_user as c on a.UserID=c.UserID

[chuifengde]

declare @a table(ItemID int, userID int, targetID int, other varchar(10))
insert @a select
1, 1, 2, 'aaab'
union all select 2, 2, 1, 'bbc'
union all select 3, null, null, 'kkkk'

declare @b table(UserID int, Name varchar(10))
insert @b select 1 ,'SSS'
union all select 2 ,'MMM'


select itemid,b.name username,c.name targetname,other from @a a left join @b b on a.userid=b.userid left join @b c on a.targetid=c.userid

[jetdw]

就是用left join就可以了

[killxtt]

left join 是个好东东

[chyliu]

select a.itemid,b.name,c.name,a.other from tb_item as a
left join
tb_user as b
on a.userid = b.userid
left join
tb_user as c
on a.targetid = c.userid