>>>Hi,
>>>
>>>This is a very newbie question, I know.
>>>
>>>I am trying to understand the SQL Select that Tamar Granor kindly helped me with. It has two JOIN clauses. And THEN, after the second JOIN, there are two ON clauses. I didn't know you can do it. I thought that ON should follow the JOIN immediately.
>>>And looks like the first ON belong to the top (first) JOIN and the second ON belongs to the bottom (second) JOIN.
>>>
>>>My question is, how does SQL Server (or VFP for that matter) knows which ON apply to which JOIN?
>>>
>>>TIA
>>
>>For all I know, there is the following syntax:
>>
>>Syntax one: The ON follows the JOIN, from top to bottom
>>
>>SELECT * ;
>>FROM Cur1 ;
>> INNER JOIN Cur2 ;
>> ON Cur1.Field=Cur2.Field ;
>> ;
>> INNER JOIN Cur3 ;
>> ON Cur2.Field=Cur3.Field
>>
>>
>>Syntax two: The ONs follows the JOINs, inside to outside
>>
>>SELECT *;
>> FROM Cur1;
>> INNER JOIN Cur2;
>> INNER JOIN Cur3;
>> ON Cur2.Field=Cur3.Field;
>> ON Cur1.Field=Cur2.Field
>>
>>
>>You are free to mix the styles. It might create a headache, but is it possible.
>>
>>Update: tab to space
>
>Lutz,
>
>Thank you very much for a clear example explaining my question. The second JOIN syntax was new to me.
>But now I see that if there are two JOIN clauses, and the second JOIN syntax is used, the first ON must be applied to the first JOIN.
??? The second example:
The JOIN between Cur2 and Cur3 (ie the second JOIN) is the first or innermost ON (Cur2.Field=Cur3.Field). It might differ on different engines, but this at least is what VFP is doing. At one moment in time I switched some hundred views from 2 to 1 (while setting up the views to CA) , so there is remembrance of this.
Words are given to man to enable him to conceal his true feelings.
Charles Maurice de Talleyrand-Périgord
Weeks of programming can save you hours of planning.
OffThere is no place like [::1]