CODEWITHSUNDEEP
sqljoinnullms-access-2013
whatwhatwhat
whatwhatwhat

Reputation: 2276

LEFT JOIN not returning NULL

I guess the problem comes down to this: what are some extreme scenarios where using a LEFT OUTER JOIN DOES NOT return the values as expected? Because in the result set I'm expecting the fields I joined on (item and ID) + any NULL values where the rows don't match, but item and ID don't show up.

Info:

qry_HersheySAPMaxDate2 returns 95 rows.

qry_HersheySAPMaxDate2 could have NULL values for MaxOfMaxOfjob_Date, SumOfSumOfqty_on_hand, product_code, and whse, whereas ID and item will always have a value.

qry_HersheySAPMaxDate3 returns 85 rows.

qry_HersheySAPMaxDate3 does not have any NULL values in any field, but excludes 10 id and item rows.

The query:

SELECT 
qry_HersheySAPMaxDate3.ID, 
qry_HersheySAPMaxDate3.item, 
qry_HersheySAPMaxDate3.MaxOfMaxOfjob_date, qry_HersheySAPMaxDate3.SumOfSumOfqty_on_hand, qry_HersheySAPMaxDate3.product_code, 
qry_HersheySAPMaxDate3.whse, 
qry_HersheySAPMaxDate3.jobnumber

FROM 
qry_HersheySAPMaxDate2 
LEFT JOIN qry_HersheySAPMaxDate3 ON (qry_HersheySAPMaxDate2.item = qry_HersheySAPMaxDate3.item) AND (qry_HersheySAPMaxDate2.ID = qry_HersheySAPMaxDate3.ID);

Result set using my query + the suggestion in one of the answers to use LEFT OUTER JOIN instead:

Screenshot

Upvotes: 1

Views: 3029

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94939

You complain about your query producing entirely blank rows. Let's see why:

You outer join qry3 to qry2. That means when there is no match for a qry2 record in qry3, then a pseudo qry3 record with all columns set to null gets joined.

In your query you select only fields from qry3, so in an outer join case they are all null. Select qry2.ID and qry2.item instead of qry3.ID and qry3.item to see the values that have no match: 

SELECT 
  qry_HersheySAPMaxDate2.ID, 
  qry_HersheySAPMaxDate2.item,

Upvotes: 1

BytesOfMetal
BytesOfMetal

Reputation: 724

You should use LEFT OUTER JOIN if you want the NULL values to be included in the result

Upvotes: 0

Related Questions