1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--normal
select Toy_Code,ToyCaption_Title from COM_Toys
left join ToyCaptions on ToyCaptions.ToyCaption_Toy_ID = Toys.Toy_ID
where ToyCaptions.ToyCaption_Language_ID=1
--join with where inline
select Toy_Code,ToyCaption_Title from COM_Toys
left join ToyCaptions on ToyCaptions.ToyCaption_Toy_ID = Toys.Toy_ID and ToyCaption_Language_ID=1
--join with select
select Toy_Code,ToyCaption_Title from COM_Toys as TBLA
left join (select * from ToyCaptions where ToyCaption_Language_ID=1) as TBLB ON TBLA.Toy_ID = TBLB.ToyCaption_Toy_ID and TBLB.ToyCaption_Language_ID=1
--using cross apply
select Toy_Code,TBLB.ToyCaption_Title from Toys
Cross Apply (select top 1 ToyCaption_Title from ToyCaptions where ToyCaption_Language_ID=1 and ToyCaption_Toy_ID= Toys.Toy_ID) as TBLB
origin - http://www.pipiscrew.com/?p=2491 sql-joins-experts