select aaa.aid,aaa.aname,bbb.bname,ccc.cname
from aaa left join
(select * from bbb b where not exists(select 1 from bbb where aid = b.aid and bid > a.bid) bbb on aaa.aid = bbb.aid
left join
(select * from ccc c where not exists(select 1 from ccc where aid = c.aid and cid > a.cid) ccc on aaa.aid = ccc.aid
from aaa left join
(select * from bbb b where not exists(select 1 from bbb where aid = b.aid and bid > a.bid) bbb on aaa.aid = bbb.aid
left join
(select * from ccc c where not exists(select 1 from ccc where aid = c.aid and cid > a.cid) ccc on aaa.aid = ccc.aid
create table AAA
([AID] [int] identity(1,1),aname [varchar](50))
on [primary]
insert into AAA (aname) values ('A1')
insert into AAA (aname) values ('A2')
insert into AAA (aname) values ('A3')
insert into AAA (aname) values ('A4')
insert into AAA (aname) values ('A5')create table BBB
([BID] [int] identity(1,1),AID int,Bname [varchar](50))
on [primary]
insert into BBB (AID,Bname) values (1,'BBNAME1')
insert into BBB (AID,Bname) values (1,'BBNAME2')
insert into BBB (AID,Bname) values (3,'BBNAME3')create table CCC
([CID] [int] identity(1,1),AID int,Cname [varchar](50))
on [primary]
insert into CCC (AID,Cname) values (1,'CCNAME1')
insert into CCC (AID,Cname) values (2,'CCNAME2')
insert into CCC (AID,Cname) values (3,'CCNAME3')
insert into CCC (AID,Cname) values (3,'CCNAME4')
insert into CCC (AID,Cname) values (4,'CCNAME5')
insert into CCC (AID,Cname) values (3,'CCNAME6')select AAA.AID, AAA.aname, BBB.Bname, CCC.Cname from
(
select AAA.*, BBB.BID, CCC.CID from AAA
left join
(select BID=max(BID), AID from BBB group by AID) BBB on AAA.AID=BBB.AID
left join
(select CID=max(CID), AID from CCC group by AID) CCC on AAA.AID=CCC.AID
)AAA
left join BBB on AAA.BID=BBB.BID
left join CCC on AAA.CID=CCC.CID--result
AID aname Bname Cname
----------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1 A1 BBNAME2 CCNAME1
2 A2 NULL CCNAME2
3 A3 BBNAME3 CCNAME6
4 A4 NULL CCNAME5
5 A5 NULL NULL(5 row(s) affected)