Keeping unmatched rows while joining on the column the rows are in MySQL-CodePudding

I have 2 tables. current_inv and qb_inventory_levels_v. I want to join those by part_number, while also allowing it to still show rows where there is only a part number in one of those tables. Ex:

part_number_a	part_number_b	qty_a	qty_b
ABCDEFG	ABCDEFG	1	3
ZYXW	null	2	null
null	WWWW	null	1

The current query I have is only giving me the ones where they overlap. I have tried looking for other answers on stackoverflow and haven't been able to find one that fits my exact problem. Thank you in advance for any help. My current query is:

SELECT * FROM (SELECT part_number, COUNT(part_number) AS current_inv_qty FROM utility_views.current_inv GROUP BY part_number) sub_2
JOIN (SELECT part_number, SUM(quantityonhand) AS qb_qty FROM qb_inventory_levels_v a GROUP BY part_number) sub_1 ON sub_1.part_number = sub_2.part_number
WHERE current_inv_qty > qb_qty

CodePudding user response：

A left join with a nullable check should work out for you.

The left join ensures that always you will get all the part_numbers as returned by the query sub_2 in your output.

It is necessary to add the condition OR qb_qty IS NULL to cater to those rows which do not have a part number in sub_1

   SELECT * 
     FROM (SELECT part_number, COUNT(part_number) AS current_inv_qty 
             FROM utility_views.current_inv 
           GROUP BY part_number) sub_2
LEFT JOIN (SELECT part_number, SUM(quantityonhand) AS qb_qty 
           FROM qb_inventory_levels_v a GROUP BY part_number) sub_1 
       ON sub_1.part_number = sub_2.part_number
    WHERE (current_inv_qty > qb_qty
       OR qb_qty IS NULL)