I have two tables, one is for all products, the other is for new purchases products.
Product Table
Item Qty
301 2
302 5
303 3
304 4
305 6
Purchases Table
Item Qty Status Date
302 5 used 09-15-2015
303 5 reserve 09-20-2015
301 5 used 09-20-2015
302 5 reserve 09-20-2015
304 5 used 10-15-2015
303 5 reserve 10-15-2015
I want to display how many quantity in the product table, and join the quantity of purchases table
to Product Table
This is my initial sql query
SELECT product_name, product_quantity, quantity
FROM products
LEFT OUTER JOIN purchases ON products.ID = purchases.product_ID
WHERE product_status !='deleted' AND status != 'used'`.
However, it only returned the the items from purchases table
that is reserve.
what I want to achieve is
Item Qty Reserved Qty Total
301 2 0 2
302 5 5 10
303 3 10 13
304 4 0 4
305 6 0 6
UPDATE New sql query
SELECT product_name, product_quantity, p.quantity
FROM products
LEFT OUTER JOIN (SELECT * FROM purchases
WHERE status = 'reserved'
GROUP BY product_ID) AS p ON p.product_ID = products.ID
WHERE products.product_status !='deleted'`
But it returns 0 on reserved qty.
You are almost there in terms of your query except that you need to sum the quantity of reserved items and then use GROUP BY
, sort of like this
SELECT
pd.item AS 'Item', pd.qty AS 'Qty', IFNULL(pr.reserved_quantity, 0) AS 'Reserved Qty', (pd.qty+IFNULL(pr.reserved_quantity, 0)) AS 'Total'
FROM
product pd
LEFT OUTER JOIN (SELECT item, SUM(qty) AS 'reserved_quantity' FROM purchases WHERE `status`='reserve' GROUP BY 1) AS pr ON pd.item=pr.item
Эта статья взята из Интернета, укажите источник при перепечатке.
Если есть какие-либо нарушения, пожалуйста, свяжитесь с[email protected] Удалить.
я говорю два предложения