Usually, IN query is the fastest in VFP. I wouldn't expect DISTINCT with JOIN to be faster but only test can prove that. You can also try correlated query.
Select * from csrA ;
WHERE EXISTS ( ;
SELECT * FROM TableB
where TableB.fieldA = TableA.fieldA ;
TableB.fieldB='101')
In general correlated query in VFP is slower than IN type.
>Sergey,
>Thank you. I am trying to avoid using IN because it really slows down my query. The tables have over million records.
>
>What do you think of using 'distinct' as I did? somehow 'distinct csrA.*' does not make a lot of sense to me, as we would usually apply distict on one field on not on the entire record. I used distinct on the entire record because, the structures of Tables A & B being different, the query would return a record from csrA whenever it found a macth in TAbleB, theryby repeating records in csrA. Any suggestions would be appreciated.
>
>Ria
--sb--