Hi!

It seems you was not very accurate when reading my message. I'm not propose to use equivalent. I just tried to explain why your query is not optimized. Read it again you will find answer there.

>>Hi!
>>
>>Because very nature of SQL, following is an equivalent using VFP code of processing of the last query:
>>
>>

>>select hpaph0
>>scan all
>>  select arappx
>>  locate for arappx.appst = (hpaph0.aphst_) AND ... && locate for join condition
>>  if found()
>>    && insert record into result set
>>    ...
>>  endif
>>  select hpaph0
>>  skip
>>endscan
>>

>>
>>As you see from the process above, optimization takes indexes from arappx table, not from main table, just because in the query main table scanned and records in the child table looked up.
>>
>>You can swap tables in join, and you will see that query is optimized than.
>
>Yes, this would be an optimized way of doing the join, but I wouldn't really call it a query...it using xBase looping instead of pure SQL. I would like to use pure SQL, as I have several tables that I need to do these types of queries on, and to have to loop through them all like above seems like a workaround. I would rather just have the query work, and am not understanding why there is no optimization on a table that clearly has tags that match what is being used in the join.
>
>If my tables were simple, I would enjoy using the above scenario, since it would be easy to build my own cursors and populate them. Problem is, the hpaph0 table has something like 240 fields, so I would have to use COPY STRUCTURE and all that jazz just to get a cursor to start with. Then run the loop above -- in total you would be talking 15-20 lines of code for each table I need to run this on. Sure, I could write a separate function, but a single line of SQL should be able to do all that is going on above. I just need to get it optimized.
>
>Sorry I am ranting here. Thank you for taking the time to devise a very workable plan. I may have to use it if my SQL idea just doesn't pan out...
>
>Anyone else have an idea on optimizing my original SQL statement, or is such a join simply non-optimizable?
>
>Thanks,
>JoeK