Ok, I found it message #
1042888 (there was another one, but not that relevant - only showing my mistake again).
>>Please Note: VFP 8
>>
>>I have a cursor (crsAllCust) with a list of customer names. There are 30 records in this table.
>>I have another cursor (crsFillRate) with some customer data in it.
>>
>>I need to join these tables to provide a count of how many records there are per customer which meet a certain criterion. I need a record returned even if there are none with a matching condition. There are three customers which have no data in crsFillRate. There are an additional two customers who have records, but without any data matching the condition.
>>
>>
>>SELECT cust.customer, COUNT(*) ;
>> FROM crsAllCust cust;
>> LEFT JOIN crsFillRate fr ON cust.customer = fr.custshipm;
>> WHERE EMPTY(fr.PartOrders);
>> GROUP BY cust.customer
>>
>>The five "nodata" records fall out of the result. By changing the query to
>>SELECT cust.customer, COUNT(*) ;
>> FROM crsAllCust cust;
>> LEFT JOIN crsFillRate fr ON cust.customer = fr.custshipm AND EMPTY(fr.PartOrders);
>> GROUP BY cust.customer
>>
>>I get all the customers.
>>
>>I think I've had this explained to me before, but apparantly it hasn't stuck (sorry Tamar). Could somebody please give me a refresher course of what the query steps are that cause the five records to fall out of the first query?
>>
>>Thanks..........Rich
>
>In the first query the WHERE condition applies after the JOIN is done thus making the query to be INNER JOIN. The second applies correctly.
>
>I can point you to some threads later on - it took me few iterations before this logic stick in mind.
If it's not broken, fix it until it is.
My Blog