>>>Almost all EXISTSs can be replaced with IN conditions. From my experience, doing this replace gives simplier and faster queries.
>>>
>>>Vlad
>>>
>>I agree. Actually, I always use IN and never EXISTS.
>
>This is interesting, I didnt even think of using IN. Problem is though for now, any code I write that has to be run on regular basis has to be supported by my FPW 2.6 App. Using IN clause in 2.6 is only supported for a value set. But thanks for the advice, I will look at using this tip when I replace the 2.6 Reporting System App with my VFP 5.0 Data Warehousing App. But, in response to Vlad's other message regarding speed, I did some testing.
>
>First off, once Ed helped me with my original queries, seems my job specification was re-written _again_. Below is the final code I used to get this job off my desk:
>
>SELE azactive.offnum, azactive.acct AS account, azactive.name FROM azactive ;
> WHERE NOT EXISTS( ;
> SELE * FROM addunit WHERE addunit.account==azactive.acct) ;
> INTO CURSOR temp1 &&Selects 875 records
>
>SELE azactive.offnum, azactive.acct AS account, azactive.name FROM azactive ;
> WHERE NOT EXISTS( ;
> SELE * FROM azhandle WHERE azhandle.account==azactive.acct) ;
> INTO CURSOR temp2 &&715 records
>
>SELE * FROM temp1 WHERE EXISTS( ;
> SELE * FROM temp2 WHERE temp2.account==temp1.account) ;
> INTO TABLE azterms &&715 records
>
>Testing run on my network workstation (instead of on faster Server):
>1st Query, FPW 2.6 = 91.56 seconds, VFP 5.0a = 184.55
>2nd Query, FPW 2.6 = 373.25 seconds, VFP 5.0a = 399.46
>3rd Query, FPW 2.6 = 2.47 seconds, VFP 5.0a = 4.34
>
>Interesting huh? I'm trying real hard not think about the fact that the 32-bit environment didnt perform as well as the 16-bit environment. ;)
>
>So then I tried to just replace the EXISTS cluases with IN and run in VFP for comparison. But I get error that says I'm missing a paranthesis? Tell me the correct way to use IN instead of EXISTS in the above code and I'll post results so we can see which is faster in this example. Much Thanks, this thread has really helped me!!

SELECT azactive.offnum, azactive.acct AS account, azactive.name FROM azactive ;
INTO CURSOR temp1 ;
WHERE acct NOT IN (SELECT account FROM addunit)

SELECT azactive.offnum, azactive.acct AS account, azactive.name FROM azactive ;
Into Cursor temp2 ;
Where acct NOT IN (Select account From azhandle)