0

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?

Wolfgang Müller
  • 386
  • 1
  • 6
  • 21
  • Your additional join is adding additional rows -- presumably one for each item. This multiplies the number of rows being aggregated. One solution is to pre-aggregate the results using the query that works. – Gordon Linoff Feb 02 '16 at 14:57
  • The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function! – jarlh Feb 02 '16 at 14:59
  • ok, can you give me an example? It should be grouped by the products ID – Wolfgang Müller Feb 02 '16 at 14:59
  • The standard way is to take the selected columns that are not arguments to set functions, and put those in the group by clause. In this case `GROUP BY name, stock`. – jarlh Feb 02 '16 at 15:08

1 Answers1

0

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786