I still don't understand joining

[WaterTight]

I need to left outer join both the Adjustments and the Payments tables to the Cases table where the CaseID's are equal. Sounds simple enough...

SELECT Cases.Units From Cases
Left Join Adjustments On
Cases.CaseID = Adjustments.CaseID
Left Join Payments On
Cases.CaseID = Payments.CaseID

According to my understanding, that should work - however, I keep getting:

syntax error, missing operator in query expression 'Cases.CaseID = Adjustments.CaseID Left Join Payments On Cases.CaseID = Payments.CaseID'

What am I doing wrong here? Also, where do you specify that tables that you're joining? is it purely in the 'On' clause? Is it in the placement of parenthesis?

thanks,
Jeff

 

[r937]

parentheses will make the syntax error go away

select Cases.Units
from ( Cases
left join Adjustments
on Cases.CaseID = Adjustments.CaseID )
left join Payments
on Cases.CaseID = Payments.CaseID

rudy

http://rudy.ca/

 

[WaterTight]

Ok - that did indeed work...but I want to know why

Can anyone point me to an FAQ or something out there that explains this?

thanks,
Jeff

 

[blom0344]

A bit more compact,

Select a.x,b.y
From a left join b on
(a.i = b.i) and (a.j = b.j);

This is also default syntax created from query panel.
T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[blom0344]

Syntax as I showed also will prevent ambiguous outer joins , such as one part relating to an outer join and the other part to an inner join. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com