2 x SUM(IF.. and 2x LEFT JOIN

[Quarco]

Hi,

Suppose I have a query like:

SELECT
products.name AS product,
SUM(IF(stock.invoice=0,1,0)) AS in_stock,
SUM(IF(shopcart.status=1,1,0)) AS reserved
FROM products
LEFT JOIN stock ON products.id=stock.product_id
LEFT JOIN shopcart ON products.id=shopcart.product_id
GROUP BY products.id

I get wrong results..
E.g. I now have 1 item 'reserved' and 5 in stock; I get 5 reserverd and 5 in
stock..
Is there a workaround for this problem??
Thanx in advance,

Marco

[Bill Karwin]

Quarco wrote:

Hi,

Suppose I have a query like:

SELECT
products.name AS product,
SUM(IF(stock.invoice=0,1,0)) AS in_stock,
SUM(IF(shopcart.status=1,1,0)) AS reserved
FROM products
LEFT JOIN stock ON products.id=stock.product_id
LEFT JOIN shopcart ON products.id=shopcart.product_id
GROUP BY products.id

I get wrong results..
E.g. I now have 1 item 'reserved' and 5 in stock; I get 5 reserverd and 5 in
stock..
Is there a workaround for this problem??

An outer join doesn't return just zero or 1 match; it returns N matches,
which result in N rows. So for example if you have 5 in stock for
product id 327, then you get 5 rows corresponding to product id 327.
Thus those 5 rows _each_ match the one item reserved.

Try this query and see what I mean:

SELECT
products.name AS product,
IF(stock.invoice=0,1,0) AS in_stock,
IF(shopcart.status=1,1,0) AS reserved
FROM products
LEFT JOIN stock ON products.id=stock.product_id
LEFT JOIN shopcart ON products.id=shopcart.product_id
ORDER BY products.id;

It works both ways too. If you have 2 reserved and 5 in_stock, then you
get 2*5 rows returned, and the sum for both in_stock and reserved
becomes 10.

I can suggest two alternatives:

1. Do each sum calculation in separate queries:

SELECT
products.name AS product,
SUM(IF(stock.invoice=0,1,0)) AS in_stock,
FROM products
LEFT JOIN stock ON products.id=stock.product_id
GROUP BY products.id;
SELECT
products.name AS product,
IF(shopcart.status=1,1,0) AS reserved
FROM products
LEFT JOIN shopcart ON products.id=shopcart.product_id
GROUP BY products.id;

2. Do the summation in subqueries (requires MySQL 4.1):

SELECT
p.name AS product,
(SELECT SUM(IF(s.invoice=0,1,0)) FROM stock AS s WHERE s.product_id
= p.id) AS in_stock,
(SELECT SUM(IF(h.status=1,1,0)) FROM shopcart AS h WHERE
h.product_id = p.id) AS reserved
FROM products AS p
ORDER BY products.id

Regards,
Bill K.