>** >** 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 >