Mysql left/inner join combination not working as expected

Question

I am trying to list product variations with their quantities ordered, BUT ALSO show the product variations where there is no quantity ordered. So I thought it would be as simple as selecting the products and doing a left join on the orders of each product where the order is a current revision.

So I expected like this order of operations:

SELECT p.product_id, SUM(po.quantity) 
FROM `products` p 
LEFT JOIN `product_orders` po ON p.product_id=po.product_id 
LEFT JOIN `orders` o ON o.order_id=po.order_id AND o.is_current='1'

but that is getting also the quantities where the is_current is not 1 Then I thought, okay, I can just do an inner join after the left join instead like this:

SELECT p.product_id, SUM(po.quantity) 
FROM `products` p 
LEFT JOIN `product_orders` po ON p.product_id=po.product_id 
INNER JOIN `orders` o ON o.order_id=po.order_id AND o.is_current='1'

but then the products which have not been ordered yet are not being listed. I expected them to to show up as SUM(quantity) being NULL. Can anyone see where my logic has gone wrong? Thanks! Scott

Answer 1

While Kaj's answer is correct, it's not necessarily ideal, as MySQL tends to skip the utilization of indexes when using derived tables (sub-selects). At least, this is my understanding. You can continue to use your methodology of using JOINS if you place the joins within parenthesis:

SELECT p.product_id, SUM(po.quantity) 
FROM `products` p 
LEFT JOIN (`product_orders` po
  INNER JOIN `orders` o ON o.order_id=po.order_id AND o.is_current='1')
ON p.product_id=po.product_id;

Just remember that the ON clause for the LEFT JOIN needs to come after the parenthesis. I hope this helps!

Answer 2

If the only product orders that count are those where it is current then you need to find that subset before you do a left join to it. Otherwise if you do a left join you either get all or only those ordered as you've discovered.

So something like the following should work:

select p.productid, sum(po.quantity)  
from products p  
left outer join (select po.productid, po.quantity  
                       from productorders po  
                       inner join orders o on o.orderid = po.orderid and o.iscurrent = 1) po on po.productid = p.productid  
group by p.productid