Join question

[djj55]

Hello, SQL2008R2
If you have a left join that in turn is joined by an inner join why do I not get all records

SELECT A.ValA1, B.Val1
FROM MyTableA A 
LEFT JOIN MyTableB B ON A.AID = B.AID
INNER JOIN MyTableC C ON B.BID = C.BID;

will only give where B matches A while using a left join to C gives what I would expect, all records from A with or without a matching B.

I you could explain or give a reference I would be greatful.
Thank you

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[imex]

Hi,

When no match of B with A, B.BID is null and therefore there will be no match with C.

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[djj55]

Thank you imex. I understand the no match with C, however why is the A record not returned?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[imex]

With an Inner Join, B would have to match with C, and it does not occur when B.BID is null.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[djj55]

So loosly stated, even though A should have all records (left join), however, because B does not have nulls (inner join to C), the A records do not show?



djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[drlex]

My take is that SQL server is first evaluating the join between tables A & B, and then joining the result set to table C. Books On Line recommend the use of parentheses to order multiple joins.
Using your example:

SELECT A.ValA1, [red][b]Z[/b][/red].Val1
FROM MyTableA A 
LEFT JOIN 
[red][b](SELECT B.Val1 FROM [/b][/red]
MyTableB B INNER JOIN MyTableC C ON B.BID = C.BID
[red][b])Z[/b][/red] ON A.AID = [red][b]Z[/b][/red].AID
;

soi là, soi carré

 

[djj55]

Thank you drlex. I must remember the parentheses. That should keep me out of trouble.

Thanks to you also imex for your information.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[drlex]

No problem - the join is everything!
I must correct my code - I omitted the field for the join:

SELECT A.ValA1, Z.Val1
FROM MyTableA A 
LEFT JOIN 
(SELECT [red][b]B.AID,[/b][/red] B.Val1 FROM 
MyTableB B INNER JOIN MyTableC C ON B.BID = C.BID
)Z ON A.AID = Z.AID
; 

[i]soi là, soi carré[/i]