By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,486 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

left join an sum

P: n/a
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
index
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

--
Rossz

Oct 25 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
ro***@vamos-wentworth.org wrote:
SELECT p.*,i.sql_inventory-SUM(oi.sql_quantity) AS quantity .... 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 AND o.sql_batch_id=0


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!

Regards,
Bill K.
Oct 28 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.