大家帮助我好吗很急,不知怎么办.....

[wangyongahz]

有3张表

A
id name Ownid
1 AA 1
2 BB 5
3 CC 2

B
Ownid Bname
1 GGG
1 HHH
2 LLL
...

C
Ownid CName
1 KK
5 PPP
...

要得到:
id name Ownid Bname Cname
1 AA 1 GGG KK
2 BB 5 PPP
3 CC 2 LLL

也就是只要A对应B,C的一行,

[jacobsan]

select id,name,a.Ownid,Bname,Cname from a left join b on a.Ownid=b.Ownid join left c on Ownid a.Ownid=c.Ownid

[冷箫轻笛]

select a.id,a.name,a.Ownid,isnull(b.Bname,'') as Bname,isnull(c.Cname,'') as Cname
from a left join (select * from b t1 where not exists(select 1 from b where Ownid = t1.Ownid and Bname < t1.Bname)b on a.Ownid= b.Ownid
left join (select * from c t1 where not exists(select 1 from c where Ownid = t1.Ownid and Cname < t1.Cname)c on a.Ownid= c.Ownid

[jacobsan]

--楼上对，改改，少了个)

select a.id,a.name,a.Ownid,isnull(b.Bname,'') as Bname,isnull(c.Cname,'') as Cname
from a left join (select * from b t1 where not exists(select 1 from b where Ownid = t1.Ownid and Bname < t1.Bname))b on a.Ownid= b.Ownid
left join (select * from c t1 where not exists(select 1 from c where Ownid = t1.Ownid and Cname < t1.Cname))c on a.Ownid= c.Ownid

[yangyangxie]

mark

[hy98521]

mark

[yangyangxie]

select a.[id],a.[name],a.Ownid,isnull(b.Bname,'') as Bname,isnull(c.Cname,'') as Cname
from a left join (select ownid,min(bname) as bname from b group by Ownid) b on a.Ownid= b.Ownid
left join (select ownid,min(cname) as cname from c group by Ownid) c on a.Ownid= c.Ownid