>>>>>>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.
>>
>>In my case, this particular SQL Select will work only on MS SQL Server. I do not support VFP DB in this particular application.
>>And so far, I have been testing the approach suggested by Tamar, it works. Not that I doubt that a young and inexperienced person like Tamar could suggest a wrong approach :)
>
>Nobody is to old to learn new stuff. We are only to old to die young. :)
True. I am too old but I am still learning. I just wish I spend more time on reading tech stuff. I read all the time, a lot; but not tech articles. Burn out :)
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham