Man, these OUTER JOINs are murder <g>.

I run the following code from the command window:

SELECT table1.id, table2.cnt;
FROM table1 ;
LEFT JOIN table2 ON table1.id=table2.id

It produces all of the 12582 records from table1 and matching cnt values for the 11757 records that are represented in table2. The 825 IDs that don't have a match in table2 are assigned .NULL. in the result table. All of this is as I would expect.

The problem is that when I cut and paste this code into my app, compile it, and run the EXE, only 11710 of the records in table2 are matched. That is, 47 records that have an ID in table2 have a value of .NULL. in the result table! How can this be??

Also, in case it helps to know that I've checked:
- there are no duplicate IDs within table1
- there are no duplicate IDs within table2
- there are no deleted records in either table
- the ID field in each table is INTEGER
- all of the IDs in table2 are represented in table1

It seems clear that something about the VFP environment is affecting the processing of the SELECT - JOIN but I can't imagine what it is. I've dumped the LIST STATUS immediately before the above command and verified that all of the SET commands are identical to the Command Window environment. In particular, DELETED is ON, ANSI is ON, and EXACT is OFF in both environments.

I've even created a TEST.VUE immediately before the SQL command proceeds to produce the wrong results. From the Command Window, I can SET VIEW TO TEST.VUE and execute the command and I get the right results!

What else could be causing the SQL to produce wrong results in my app? Should it really matter (as to results, not speed) whether table1 and table2 are open elsewhere or what indexes are present? I can't think of anything else.

I'm starting to lose hair!

Thanks for any help.

-Bob