Hey all,

Take the following SQL statement:

SELECT hpaph0.* FROM hpaph0 ;
WHERE hpaph0.aphst_ = 34 ;
AND hpaph0.aphcty = 77 ;
AND hpaph0.aphpol = 60051 ;
AND hpaph0.aphyr = 1 ;
INTO CURSOR cResult

Using SYS(3054,11) VFP (version 6, SP3) tells me that table hpaph0 is FULLY optimized. This is to be expected since index tags exist for aphst_, aphcty, aphpol, and aphyr.

Now, take the following:

SELECT hpaph0.* FROM hpaph0, arappx ;
WHERE hpaph0.aphst_ = arappx.appst ;
AND hpaph0.aphcty = arappx.appcty ;
AND hpaph0.aphpol = arappx.apppol ;
AND hpaph0.aphyr = arappx.appcyr ;
INTO CURSOR cResult

The only difference here is that I have added a table (to do a join) called arappx. Instead of using literal values I am using fields from arappx in order to do a join. For this, VFP tells me that table hpaph0's optimization is NONE. What gives? I was under the impression that as long as Fox could match what was on the left side of the comparisons to index tags, some Rushmore would kick in, at least for one of the tables. Am I high? The table arappx in this example has no indexes whatsoever, but shouldn't Rushmore still be able to optimize because of the hpaph0 tags? The arappx table is only around 5-10 records.

If I am just way off, how does one do an optimized join such as this? I tried using INNER JOIN syntax, thusly:

SELECT hpaph0.* FROM hpaph0 INNER JOIN arappx ;
ON hpaph0.aphst_ = arappx.appst ;
AND hpaph0.aphcty = arappx.appcty ;
AND hpaph0.aphpol = arappx.apppol ;
AND hpaph0.aphyr = arappx.appcyr ;
INTO CURSOR cResult

And got the same response -- zero optimization. Could somebody enlighten me as to where I am going wrong here?

Thanks muchly.

JoeK