Left Join works with table but fails with query

Posted by Frank Martin on Stack Overflow See other posts from Stack Overflow or by Frank Martin
Published on 2012-03-28T16:32:45Z Indexed on 2012/03/29 11:30 UTC
Read the original article Hit count: 238

Filed under:
|
|

The following left join query in MS Access 2007

SELECT
Table1.Field_A,
Table1.Field_B,
qry_Table2_Combined.Field_A,
qry_Table2_Combined.Field_B,
qry_Table2_Combined.Combined_Field
FROM Table1
LEFT JOIN qry_Table2_Combined
 ON (Table1.Field_A = qry_Table2_Combined.Field_A) 
AND (Table1.Field_B = qry_Table2_Combined.Field_B);

is expected by me to return this result:

+--------+---------+---------+---------+----------------+
|Field_A | Field_B | Field_A | Field_B | Combined_Field |
+--------+---------+---------+---------+----------------+
|1       |         |         |         |                |
+--------+---------+---------+---------+----------------+
|1       |         |         |         |                |
+--------+---------+---------+---------+----------------+
|2       |1        |2        |1        |John, Doe       |
+--------+---------+---------+---------+----------------+
|2       |2        |         |         |                |
+--------+---------+---------+---------+----------------+

[Table1] has 4 records, [qry_Table2_Combined] has 1 record.

But it gives me this:

+--------+---------+---------+---------+----------------+
|Field_A | Field_B | Field_A | Field_B | Combined_Field |
+--------+---------+---------+---------+----------------+
|2       |1        |2        |1        |John, Doe       |
+--------+---------+---------+---------+----------------+
|2       |2        |2        |         |,               |
+--------+---------+---------+---------+----------------+

Really weird is that the [Combined_Field] has a comma in the second row. I use a comma to concatenate two fields in [qry_Table2_Combined].

If the left join query uses a table created from the query [qry_Table2_Combined] it works as expected.

Why does this left join query not give the same result for a query and a table? And how can i get the right results using a query in the left join?

© Stack Overflow or respective owner

Related posts about sql

Related posts about ms-access