>Thanks, Nadya. Now, as I mentioned in other parts of this thread, the actual SQL - SELECT command is more complicated.
>
>About your FAQ, I think you should mention the alternative of a subquery. The code is much shorter and - once you understand subqueries - simpler. I don't know about speed; but sometimes you want to get results quickly, meaning, without writing a lot of code!
>
>In your example, you can show the results in a browse window with a single command:
>
>

>select * from Table2 where KeyId not in (select KeyId from Table1)
>

>
>What I am dreaming about is to integrate subqueries with the SCAN (or something similar). I think this can be done with a UDF, but I have to think about it a little more.
>
>Regards, Hilmar.

Hi Hilmar,

We tried it, but it's slower, than the methods described. Less code is not always correspond to the most efficient method.

>>Hilmar,
>>
>>Check my FAQ "Best way to find orhpans". You can use either:
>>
>>scan for indexseek(KeyField,.f.,'Table2','KeyIndex')
>>endscan
>>
>>of using KeyMatch, which would be faster.
>>
>>Another way would be to set relation and
>>scan for found()
>>endscan
>>
>>
>>>I want to process, in a SCAN loop, all records that satisfy a given condition. The condition is most easily expressed as a SELECT - SQL - but what is the best way to combine the two?
>>>
>>>

>>>Pseudocode:
>>>
>>>SCAN where KeyField in (SELECT KeyField from AnotherTable)
>>>   ...
>>>ENDSCAN
>>>

>>>
>>>The processing itself is too complicated to place into a single INSERT - SQL command.
>>>
>>>Basic criteria: 1) code should not be too messy, 2) it should be reasonably fast.
>>>
>>>TIA, Hilmar.