Search code examples
mysqlsqljoinleft-joinright-join

MYSQL SUM qty with Join

i have a problem with my SQL query. When I use more then one JOIN, the result is wrong.

SELECT name, stock,SUM(order_items.qty),
SUM(shipping_items.qty) FROM shipping_items

JOIN mnm_products 
ON mnm_products.id = shipping_items.product_id

JOIN order_items
ON mnm_products.id = order_items.product_id

GROUP BY mnm_products.id

When i use only

SELECT name, stock,
SUM(shipping_items.qty) FROM shipping_items

JOIN mnm_products 
ON mnm_products.id = shipping_items.product_id
GROUP BY mnm_products.id

it´s right. But when i use 2 joins. the result of both are the same. Can anybody help me there?

Solution

  • You may be best off with correlated subqueries:

    select p.*,
       (select sum(si.qty)
        from shipping_items si
        where si.product_id = p.id
       ) as shipping_items,
       (select sum(oi.qty)
        from order_items oi
        where oi.product_id = p.id
       ) as order_items
from mnm_products p;