Microsoft KB Archive/268022

= SELECT-SQL returns invalid rows when filtering outer join on child table =

Article ID: 268022

Article Last Modified on 4/15/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition

-



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 &quot;References&quot; section to follow.



Steps to Reproduce Behavior
  Run the following code in Visual FoxPro: CREATE TABLE PTable ( PKey C(5), ID_Field_p C(5) )

CREATE TABLE CTable ( PKey C(5), ID_Field_c C(5), TestField C(5) )
 * Create the child table.

INSERT INTO PTable VALUES ( &quot;00001&quot;, &quot;prec1&quot; ) INSERT INTO PTable VALUES ( &quot;00002&quot;, &quot;prec2&quot; ) INSERT INTO PTable VALUES ( &quot;00003&quot;, &quot;prec3&quot; ) INSERT INTO PTable VALUES ( &quot;00004&quot;, &quot;prec4&quot; ) INSERT INTO PTable VALUES ( &quot;00005&quot;, &quot;prec5&quot; )
 * Insert records into the parent table.

INSERT INTO CTable VALUES ( &quot;00001&quot;, &quot;crec1&quot;, &quot;ONE&quot; ) INSERT INTO CTable VALUES ( &quot;00002&quot;, &quot;crec2&quot;, &quot;&quot; ) INSERT INTO CTable VALUES ( &quot;00002&quot;, &quot;crec3&quot;, &quot;TWO&quot; ) INSERT INTO CTable VALUES ( &quot;00004&quot;, &quot;crec4&quot;, &quot;THREE&quot; ) INSERT INTO CTable VALUES ( &quot;00005&quot;, &quot;crec5&quot;, &quot;&quot; )
 * Insert records into the child table.
 * Note that parent record two has two children, and
 * parent record three has no children.

SELECT * ; FROM PTable ; LEFT OUTER JOIN CTable ON PTable.PKey == CTable.PKey ; WHERE CTable.TestField = &quot;    &quot; This should only match parent records 2 and 5. Because parent record 3 has no children, it should not be returned.

  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 ( &quot;00001&quot;, &quot;prec1&quot; ) INSERT INTO PTable VALUES ( &quot;00002&quot;, &quot;prec2&quot; ) INSERT INTO PTable VALUES ( &quot;00003&quot;, &quot;prec3&quot; ) INSERT INTO PTable VALUES ( &quot;00004&quot;, &quot;prec4&quot; ) INSERT INTO PTable VALUES ( &quot;00005&quot;, &quot;prec5&quot; )

INSERT INTO CTable VALUES ( &quot;00001&quot;, &quot;crec1&quot;, &quot;ONE&quot; ) INSERT INTO CTable VALUES ( &quot;00002&quot;, &quot;crec2&quot;, &quot;&quot; ) INSERT INTO CTable VALUES ( &quot;00002&quot;, &quot;crec3&quot;, &quot;TWO&quot; ) INSERT INTO CTable VALUES ( &quot;00004&quot;, &quot;crec4&quot;, &quot;THREE&quot; ) INSERT INTO CTable VALUES ( &quot;00005&quot;, &quot;crec5&quot;, &quot;&quot; )

SELECT * FROM PTable LEFT OUTER JOIN CTable ON PTable.PKey = CTable.PKey WHERE CTable.TestField = &quot;    &quot; 

