Hi Mike,

if you want a standard SQL use this:

SELECT DISTINCT A.FIELD1, A.FIELD2 AS MYFIELD1, B.FIELD1, 'TEST' AS MYFIELD2

FROM TABLE1 A JOIN TABLE2 B ON A.FIELD3=B.FIELD2 AND

A.FIELD4 IN (1234,2345...max 26 elements) OR A.FIELD4 IN (...) OR ...

attention to use !DELETED("TABLE1") because it refer to the TABLE1 workarea, not to the A
SQL member.
Use SET DELETED ON/OFF, if you want !DELETED("A") and DELETED("B") the problem is little more complex.

IN (items ) solution is the faster for a small number of items.

A way for not use macro for fill SQL command with values list, is to use a array ( max 65000, but 100 is max usefull):

  aList[1]=1234
  aList[2]=2345
  ....
  or a fill loop

next use :

SELECT DISTINCT A.FIELD1, A.FIELD2 AS MYFIELD1, B.FIELD1, 'TEST' AS MYFIELD2
FROM TABLE1 A JOIN TABLE2 B ON A.FIELD3=B.FIELD2 AND ASCAN(aList,A.FIELD4)>0

for large list ( see MIKE Y.) WHERE A.FIELD4 IN (SELECT ... ) is best because VFP build a temporary index ( overhead is gained on search )

Fabio