>>>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?
>>
>>First, in the example we've been working, while there are multiple joins, they don't belong to the same query. Here's the version I think you settled on (without the WHERE clause for filtering the dates):
>>
>>

>>SELECT <all the fields you need> ;
>>   FROM MyTable ;
>>      JOIN (SELECT MAX(ORDER_NO) AS MaxOrd FROM ;
>>                    MyTable MTB;
>>                       JOIN (SELECT MAX(DATE_FLD) As MaxDate FROM MyTable MTC) csrMaxDate ;
>>                          ON MTB.DateFld = csrMaxDate.MaxDate) csrMaxOrdNo ;
>>         ON MyTable.ORDER_NO = csrMaxOrdNo.MaxOrd
>>

>>
>>This has nested queries. You have to work from the inside out. The innermost query is:
>>
>>

>>SELECT MAX(DATE_FLD) As MaxDate FROM MyTable MTC
>>

>>
>>No join clause at all, since it's just one table. When you look outside the parentheses that surround this one, you see that it's creating a (temporary) cursor named csrMaxDate. The next query out joins csrMaxDate to the original table:
>>
>>

>>SELECT MAX(ORDER_NO) AS MaxOrd FROM ;
>>                    MyTable MTB;
>>                       JOIN (SELECT MAX(DATE_FLD) As MaxDate FROM MyTable MTC) csrMaxDate ;
>>                          ON MTB.DateFld = csrMaxDate.MaxDate
>>

>>
>>Just one join in this query, joining the MyTable using alias MTB with csrMaxDate. This query creates a (temporary) cursor named csrMaxOrdNo. Finally, the main query joins MyTable to csrMaxOrdNo:
>>
>>

>>FROM MyTable ;
>>      JOIN (SELECT MAX(ORDER_NO) AS MaxOrd FROM ;
>>                    MyTable MTB;
>>                       JOIN (SELECT MAX(DATE_FLD) As MaxDate FROM MyTable MTC) csrMaxDate ;
>>                          ON MTB.DateFld = csrMaxDate.MaxDate) csrMaxOrdNo ;
>>         ON MyTable.ORDER_NO = csrMaxOrdNo.MaxOrd
>>

>>
>>As I write this, I realize that since it's SQL Server, we could have used CTEs to make the whole thing more readable like this:
>>
>>

>>WITH csrMaxDate (MaxDate) AS 
>>   (SELECT MAX(DATE_FLD) As MaxDate FROM MyTable), 
>>
>>csrMaxOrdNo (MaxOrd) AS
>>  (SELECT MAX(ORDER_NO) AS MaxOrd 
>>    FROM MyTable 
>>        JOIN csrMaxDate 
>>          ON MyTable.DateFld = csrMaxDate.MaxDate)
>>
>>SELECT <all the fields you need> ;
>>   FROM MyTable 
>>      JOIN csrMaxOrdNo 
>>        ON MyTable.ORDER_NO = csrMaxOrdNo.MaxOrd
>>

>>
>>I think that's a lot more readable and easier to understand.
>>
>>Finally, some reading for you. I've written about the different ways to set up joins several times. This one covers the two different join styles and talks about subqueries, which is what the version at the top here uses: http://www.tomorrowssolutionsllc.com/ConferenceSessions/Joins%20and%20Subqueries-Using%20SQL%20commands%20for%20the%20Hard%20Stuff.pdf.
>>
>>As for CTEs, this seems to the first article where I talked about them and explained how they work: http://www.tomorrowssolutionsllc.com/Articles/Handling%20Hierarchical%20Data.pdf. There's also a whole subsection on them in this paper: http://tomorrowssolutionsllc.com/ConferenceSessions/Learn%20to%20use%20SQL.PDF
>>
>>Tamar
>
>Thank you very much for the detailed explanation. And for the links to your articles. Your descriptions really helps to understand. I already printed one of your articles on Handling Hierarchical Data.
>As to CTE, I don't know if this will work on my PC since I still use SQL Server 2008 Developers Edition. I may try a simple example just to make sure the CTE works on this version of SQL Server and I can test a different approach.
>Again, thank you!

CTEs were added in SQL Server 2005, so 2008 is fine.

Tamar