help with a query

[mic2100]

Hi,

i have been working on this for the last couple of nights and i don't seem to be getting very far with it. first let me explain a little about what i am trying to do.

I have got to table one to manage products and another to manage product transactions.

---------------------
TABLE: site_products
---------------------
product_id
product_name
product_desc
product_cost
product_minlvl

------------------------
TABLE: site_transactions
------------------------

trans_id
trans_product (FK)
trans_despatchoffice
trans_amount (example a +40 would add 40 items to stock and -40 would take 40 items out of stock)

I am trying to do a query that will allow me to work out if the current stock level is below the minimum level.

I had been trying something like this...


SELECT
site_products.product_name,
site_products.product_cost,
site_products.product_minlvl,
SUM(site_trans.trans_inventory) AS total_level
FROM
site_trans
INNER JOIN site_products ON (site_trans.trans_product = site_products.product_id)
WHERE
(SUM(site_trans.trans_amount) < site_products.product_minlvl)
GROUP BY
site_products.product_name


but i can't seem to get it to work, basically i am trying to compare the sum of all the transactions against the minimum stock level and if it is below that level then the record is displayed.

i think i may need to do a nested query or a union but i have never had to do any of these before, so any help would be greatly received.

[Fumigator]

You need to point your GROUP BY at the table with multiple rows with the same product_id in it. You can then join (or subquery) the site_products in, but just for the purposes of getting the product name for display purposes and the minimum level for comparison purposes.


SELECT t.trans_product, p.product_name, SUM(t.trans_amount)
FROM site_transactions as t
JOIN site_products as p
ON t.trans_product = p.product_id
GROUP BY t.trans_product
HAVING SUM(t.trans_amount) < p.product_minlvl


Might need some tweaking but I believe this general idea will get you there.