SELECT-SQL returns invalid rows when filtering outer join on child table (268022)


The information in this article applies to:

  • Microsoft Visual FoxPro for Windows 5.0
  • Microsoft Visual FoxPro for Windows 5.0a
  • Microsoft Visual FoxPro for Windows 6.0
This article was previously published under Q268022

SYMPTOMS

The SELECT - SQL command may return .NULL. records in a left-outer join when you filter the child table for EMPTY() records. This is not the way that SQL Server handles identical data and statements.

RESOLUTION

If you want to filter on the child table and only see matching records, you should use an INNER JOIN. However, if you want to see all parent records but only those children that match the filter, you should include the filter condition in the JOIN clause. For more information, see the Microsoft Knowledge Base article in the "References" section to follow.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Run the following code in Visual FoxPro:
    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 PTable ;
   LEFT OUTER JOIN CTable ON PTable.PKey == CTable.PKey ;
   WHERE CTable.TestField = "     "
    This should only match parent records 2 and 5. Because parent record 3 has no children, it should not be returned.

  2. Run the following code in the SQL Server Query Analyzer for comparison:
    CREATE TABLE PTable ( PKey Char(5), ID_Field_p Char(5) )

CREATE TABLE CTable ( PKey Char(5), ID_Field_c Char(5), TestField Char(5) )

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 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 PTable 
   LEFT OUTER JOIN CTable ON PTable.PKey = CTable.PKey
   WHERE CTable.TestField = "     "

REFERENCES

For additional information about filtering in JOIN clauses, click the article number below to view the article in the Microsoft Knowledge Base:

268906 HOWTO: Filter in a JOIN Condition

For additional information about the SELECT command, see the Visual FoxPro Language Reference and the SQL Server Books Online.

Modification Type:MinorLast Reviewed:4/15/2005
Keywords:kbCodeSnippet kbDatabase kbpending kbprb kbSQLProg KB268022
©2004 Microsoft Corporation. All rights reserved.