LEFT JOIN missing records

[nestoc]

I have 2 tables: PARTS and INVLOC. PARTS contains 15,413 records

When I run the following query, I am only getting 12,889 records returned:

SELECT PARTS.*, INVLOC.qty_on_hand
FROM PARTS LEFT JOIN INVLOC ON PARTS.part_num = INVLOC.item_no
WHERE INVLOC.loc = 'EX'
ORDER BY PARTS.ID;

I am missing all my PARTS records that do not have a corresponding item_no in INVLOC. But this is not how I understood a LEFT JOIN to work. I want all of the records in PARTS, and qty_on_hand only when it exists. I am sure I must be missing something fundamental here, this is such a simple query. Can anyone point me in the right direction?

 

[plog]

You almost understood left joins, but you missed a logical consequence: When you apply criteria to a field in a LEFT JOIN table you have effectively undone the LEFT JOIN and made it an INNER JOIN.

How is INVLOC.loc going to equal 'EX' if there is no record at all in INVLOC? It won't, so it doesn't get returned.

So, to resolve this you must decide--do you want to use that criteria or not? Does it need to expand to also include NULL INVLOC.loc values?

 

[nestoc]

Wow, I can't believe I haven't bumped my head against this before. I imagined that the WHERE criteria were being applied only to the INVLOC table before the join.

Thank you!

 

[CJ_London]

to 'restore' the left join modify your criteria to

WHERE INVLOC.loc = 'EX' OR INVLOC.loc is null

Ah - see plog has already suggested that