I can't remember seeing a multiple join query ever get better than partial optimization, but then I haven't checked the optimization level of every query. This paragraph from VFP Help could be interpreted either way:
Use Rushmore to optimize data access according to the number of tables involved. When you access single tables, you can take advantage of Rushmore anywhere that a FOR clause appears. When you access multiple tables, SELECT - SQL queries supersede all Rushmore optimizations. In an SQL command, Visual FoxPro decides what is needed to optimize a query and does the work for you. You don't need to open tables or indexes. If SQL decides it needs indexes, it creates temporary indexes for its own use.
>
>It is true that, occasionally, we know better than the optimizer. But I still don't see why you said that a query involving more than two tables could only be partially optimized.
>
>On the question of temp indexes, I'm not sure. I don't think a query ever joins tables using anything other than an index. Certainly, I've never seen that result from SYS(3054), with the exception of a cartesian join.
>
>Tamar
Craig Berntson
MCSD, Microsoft .Net MVP, Grape City Community Influencer