left join an sum

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have this query to get the 10 most common products from in stock,
based upon which parent category they are in:

SELECT  p.*,i.sql_inventory AS quantity
FROM products AS p
JOIN inv_cnt AS i ON p.sql_id=i.sql_product_id
JOIN catxprod AS x ON p.sql_id=x.sql_product_id -- product/category
JOIN cats AS c ON x.sql_cat_id=c.sql_id -- category
JOIN cats AS c2 ON c.sql_parent_id=c2.sql_id -- parent category
WHERE c2.sql_id=1 -- parent category is GUITARS in this case
AND i.sql_inventory>1 -- at least two in stock
GROUP BY p.sql_id
ORDER BY i.sql_inventory DESC
LIMIT 0,10

However, this does not take into account unprocessed orders.  I want
adjust the quantity by pending/unprocessed orders.  Orders and items
are in two additional tables, the joins were added after the existing
joins.  Since there may not be any pending orders for an item, I
thought a LEFT JOIN would do the job.

LEFT JOIN orditems AS oi ON p.sql_id=oi.sql_product_id
LEFT JOIN orders AS o ON oi.sql_order_id=o.sql_id

With a slight modification to quantity column selection:

SELECT  p.*,i.sql_inventory-SUM(oi.sql_quantity) AS quantity

Except that grabs all orders, not just unprocessed orders and I end up
with a negative quantity (and the product not getting selected).  Since
processed orders have nonzero for o.sql_batch_id.  I tried changing the
join to this:

LEFT JOIN orders AS o on oi.sql_order_id=o.sql_id AND o.sql_batch_id=0

But that didn't work.  It still grabbed all orders.

Any hints?  A summary:

Need the most common items in stock, subtracting the count for pending
orders for that product.  Pending orders for a product may not exist.

Any help would be appreciated


Re: left join an sum

rossz@vamos-wentworth.org wrote:
Quoted text here. Click to load it

You need to make sure you have an inner join between o and oi.
Right now you have an outer join to both, which is why you're getting
all the orditems.

Try this:

  SELECT  p.*,i.sql_inventory-SUM(oi.sql_quantity) AS quantity
  LEFT JOIN orditems AS oi ON p.sql_id=oi.sql_product_id
  INNER JOIN orders AS o ON oi.sql_order_id=o.sql_id AND o.sql_batch_id=0

I'm not 100% sure, because I haven't tried it to make sure.  It's a
subtle problem!

Bill K.

Site Timeline