While I can't really explain what's going on with the Indexing, the 2nd SQL statement appears to work if you use the EMPTY() function. In fact, you should always use this function (IMO) instead of an empty string because of the way VFP compares strings (see Jim's post).

>VFP 6.0, SP3
>
>I've been tracking down a problem related to an SQL statement that uses a Left Outer Join. I have a parent table that is Left Outer Joined with a child table. Also, there is a Where clause with a condition on a field in the child table, and there happens to be an index tag on that field which can be used for optimization. The Where condition is "Child.Field = ' '" (blanks). What happens is all parent records are selected, regardless of the value of Child.Field. If I remove the index, the Select works as I expect.
>
>What I've noticed in general in VFP SQL, for "phantom" child records that are created for parents w/o children, the Select results will show .NULL. for fields from the child table, but when accessing those fields in the Where clause, they don't appear to be .NULL., but empty values (character fields are spaces, numbers are 0, etc.).
>
>Now, what appears to be happening is that when the index tag exists, it's almost like the Where clause is executed first, and the child records with non-blank fields are removed. Then, the Left Outer Join is performed, and those parents that used to have children but now don't as a result of the Where clause are Left Outer Joined with a "phantom" record from the child table. When the index tag doesn't exist, things seem to be behaving properly.
>
>Does anyone have a clue what may be going on here, and if there is any way I may be able to work around the problem, other than remove all of my indexes from child tables? Following is some code I created to demonstrate this problem if anyone is interested in running it.
>
>Thanks,
>Jonathan Cochran
>
>
>
>

>**
>** This program will demonstrate a problem with Visual FoxPro 6.0 SP3 SQL that uses a
>**  Left Outer Join, and there is a Where condition on a child field and that child
>**  field has an index tag.
>**
>
>CLOSE DATABASES ALL
>
>** Create the parent table.
>CREATE TABLE PTable ( PKey C(5), ID_Field_p C(5) )
>
>** Create the child table.
>CREATE TABLE CTable ( PKey C(5), ID_Field_c C(5), TestField C(5) )
>
>** Insert records into the parent table.
>INSERT INTO PTable VALUES ( "00001", "prec1" )
>INSERT INTO PTable VALUES ( "00002", "prec2" )
>INSERT INTO PTable VALUES ( "00003", "prec3" )
>INSERT INTO PTable VALUES ( "00004", "prec4" )
>INSERT INTO PTable VALUES ( "00005", "prec5" )
>
>** Insert records into the child table.
>** Note that parent record two has two children, and
>**  parent record three has no children.
>INSERT INTO CTable VALUES ( "00001", "crec1", "ONE" )
>INSERT INTO CTable VALUES ( "00002", "crec2", "" )
>INSERT INTO CTable VALUES ( "00002", "crec3", "TWO" )
>INSERT INTO CTable VALUES ( "00004", "crec4", "THREE" )
>INSERT INTO CTable VALUES ( "00005", "crec5", "" )
>
>** Select from the parent table, Left Outer Joined with the child table.
>** Only select records where field TestField in the child table is blank.
>**  Note that this picks up parent record 3, since there is no matching
>**  child record and a Left Outer Join was done.
>SELECT * ;
>   FROM PTable ;
>   LEFT OUTER JOIN CTable ON PTable.PKey == CTable.PKey ;
>   WHERE CTable.TestField = "     "
>
>** Now, here's the problem.  Create an index tag on the child field that is
>**  referenced in the Where clause.
>SELECT CTable
>INDEX ON TestField TAG TestField
>
>** Repeat the same query.  You SHOULD get the same results, but you don't.
>** This select will get all joined records from the parent and child tables,
>**  regardless of the value of the TestField field in the child table.
>** It almost seems like the Where clause is being performed first, which
>**  throws out the records from the child table where TestField is not blank.
>**  Then, the Join is performed, and the parent records that don't have a child
>**  record now due to the Where clause are Left Outer Joined and a "phantom"
>**  child record is produced.  These records will now be picked up by the
>**  query, since the TestField = "     " condition will be true for the
>**  "phantom" records.
>SELECT * ;
>   FROM PTable ;
>   LEFT OUTER JOIN CTable ON PTable.PKey == CTable.PKey ;
>   WHERE CTable.TestField = "     "
>
>** Just to verify, delete the index tag that was created above.
>SELECT CTable
>DELETE TAG TestField
>
>** Now, re-run the query, and you get the correct results again.
>SELECT * ;
>   FROM PTable ;
>   LEFT OUTER JOIN CTable ON PTable.PKey == CTable.PKey ;
>   WHERE CTable.TestField = "     "
>
>CLOSE DATABASES ALL
>