MySQL Left Join on Inline Views – No Rows Returned Without Values

join;MySQLview

For some reason when I execute the code below I'm only getting results back that have sku in both views. I thought this should give me everything in the first view and add the content from the second view if it was there. I'd like to be able to use 0 as the value for po.tot2 below if oli.sku is not in the po view.

SELECT
   oli.sku,
   oli.tot as oli_tot,
   po.tot2 as po_tot,
   oli.tot - po.tot2 as diff 
FROM (
   SELECT
      sku,
      COALESCE(SUM(quantity),0) as tot
   FROM orderlineitems
   GROUP BY sku
) oli
LEFT JOIN (
    SELECT
       sku,
       COALESCE(SUM(amount),0) as tot2
    FROM poentries
    GROUP BY sku
) po
ON oli.sku = po.sku
WHERE oli.tot - po.tot2 > 0;

Best Answer

You're using po in the WHERE clause, and in effect, saying that it can't be null.

Maybe use: IFNULL(po.tot2,0) instead of po.tot2

Related Question