>>There're at least 3 ways you can do that
>>
>>* 1 - EXISTS subquery
>>SELECT * FROM mytable1 WHERE NOT EXISTS (SELECT 1 FROM mytable2 WHERE myfield1 = mytable1.myfield1)
>>* 2 - JOIN
>>SELECT mytable1.* FROM mytable1 JOIN mytable2 ON mytable2.myfield1 = mytable1.myfield1 ;
>> WHERE mytable2.myfield1 IS NULL
>>* 3 - IN subquery
>>SELECT * FROM mytable1 WHERE myfield1 NOT IN (SELECT myfield1 FROM mytable2)
>>
>>The second one most likely will be the fastest in VFP
>>
>
>
>Shouldn't #2 be a left join ?
Yes, it should be a LEFT JOIN (was a typo, I assume). Check this link
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1#4
If it's not broken, fix it until it is.
My Blog