Left Outer Join Producing Incorrect Results

[Kirk P.]

I've got this inner join query that correctly produces 8 records, each record
reporting the contents of the FLAG field in quniERRORS_COMBINED.

SELECT h.BATCH_ID,
h.PLATFORM_ID,
h.CANCEL_REQUEST_ID,
e.FLAG
FROM HEADER AS h
INNER JOIN quniERRORS_COMBINED AS e ON (h.CANCEL_REQUEST_ID = e.ID_FIELD)
AND (h.PLATFORM_ID = e.PLATFORM)
WHERE (((h.BATCH_ID)="300192"));

Doing nothing else but changing this to an outer join correctly produces 59
records, but based on the above query I would expect only 8 of them to have
something in the FLAG field. However, all 59 have something in the FLAG
field. How can this be?

SELECT h.BATCH_ID,
h.PLATFORM_ID,
h.CANCEL_REQUEST_ID,
e.FLAG
FROM HEADER AS h
LEFT JOIN quniERRORS_COMBINED AS e ON (h.CANCEL_REQUEST_ID = e.ID_FIELD)
AND (h.PLATFORM_ID = e.PLATFORM)
WHERE (((h.BATCH_ID)="300192"));

 

[KARL DEWEY]

I would expect only 8 of them to have something in the FLAG field.
Why? You did not test FLAG field in either query.

The difference between the two queries is that in the latter some of
quniERRORS_COMBINED.ID_FIELD or quniERRORS_COMBINED.PLATFORM may be null.

 

[Kirk P.]

Taking the results of quniERRORS_COMBINED and making a table out of that
data, then running this SQL, which simply substitutes tblERRORS_COMBINED for
quniERRORS_COMBINED:

SELECT h.BATCH_ID,
h.PLATFORM_ID,
h.CANCEL_REQUEST_ID,
t.FLAG
FROM HEADER AS h
LEFT JOIN tblERRORS_COMBINED AS t ON (h.CANCEL_REQUEST_ID = t.ID_FIELD)
AND (h.PLATFORM_ID = t.PLATFORM)
WHERE (((h.BATCH_ID)="300192"));

This produces the correct (and expected) results of 59 total records, 8 of
which are found in the ERRORS_COMBINED table and providing that tables'
"FLAG" value. Is there some reason why a union query can't be used in this
manner? I know I've done it before, but this is the first time I have seen
this behavior.

 

[KARL DEWEY]

Is there some reason why a union query can't be used in this manner?
You lost me. This is not a union query.

You keep mentioning FLAG field but you did not set any criteria for it,
unless you did something in the tblERRORS_COMBINED or query that fed it.

An INNER join, the one you said that produced 8 records must have records
matching in both tables.

A LEFT join (pulled 59 records) displays all records of the left table even
if there is no match with the right table.

Has this been helpful?