>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