>>>>>>>Hi,
>>>>>>>
>>>>>>>Here is a pseudo SQL Select statement:
>>>>>>>
>>>>>>>

>>>>>>>select field1, field2, ... from Table1 left join Table2 on Table1.fieldX = Table2.fieldX where (Long Expression)
>>>>>>>

>>>>>>>
>>>>>>>From running this type of SQL Select and analyzing results, it seems to me that VFP will first do the LEFT JOIN and then apply the WHERE condition to the resulting query. Is this correct?
>>>>>>>
>>>>>>>My question is, how do you make the SQL Select to select records from Table1 based on the Long Expression and then LEFT JOIN the resulting query?
>>>>>>
>>>>>>Add any conditions from the long expression that involve fields of Table2 to the ON clause.
>>>>>>
>>>>>>

>>>>>>SELECT field1, field2, ... ;
>>>>>>  FROM Table1 ;
>>>>>>    JOIN Table2 ;
>>>>>>      ON Table1.FieldX = Table2.FieldX ;
>>>>>>       AND Table2.FieldY > Something ;
>>>>>>  WHERE Table1.FieldZ = SomethingElse
>>>>>>

>>>>>>
>>>>>>Tamar
>>>>>
>>>>>Hi Tamar,
>>>>>
>>>>>this rule is not valid in all cases.
>>>>
>>>>Other than my forgetting the LEFT keyword, in what case of a left join will it not work to put conditions involving the "some" side of the join into the ON clause?
>>>>
>>>>Tamar
>>>
>>>This is the minimal example that show the difference
>>>

>>>CREATE CURSOR x (a i,e i)
>>>CREATE CURSOR y (b i,f I)
>>>
>>>INSERT INTO x VALUES ( 1,2 )
>>>INSERT INTO x VALUES ( 2,3 )
>>>
>>>INSERT INTO y VALUES ( 1,1 )
>>>
>>>SomethingCondition = "Y.f IS NULL"
>>>SELECT * FROM X LEFT JOIN Y ON X.a = Y.b WHERE &SomethingCondition
>>>
>>>SELECT * FROM X LEFT JOIN Y ON X.a = Y.b AND &SomethingCondition
>>>
>>>SELECT * FROM X JOIN Y ON X.a = Y.b AND &SomethingCondition
>>>

>>
>>That's a really ambiguous question to ask. That is, I'm not sure what result you want in that case.
>>
>>Tamar
>
>None ask.
>I don't want a result.
>This is an example where move WHERE SomethingCondition into the ON change the query mean.

Sure. And in fact, in the original case in this thread, it does, too, but in that case, it provides the desired answer. Let me see whether I can say it more clearly.

In a query involving an outer join, if you want to filter out some records from the "some" side of the outer join based on their actual data, you need to include that condition in the ON clause.

Tamar