Hi Fabio

Thanks for the response. I'll investigate your suggestions, but like I asked/mentioned to Mike Yearwood, I don't believe some of the ideas will work for ORACLE and SQL Server would they? Eg. ASCAN()

Mike


>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