I have these 3 tables here:
producttbl:
p_id p_name price
ANK001 ANKLET 200
BRC001 BRACELET 100
stocks_add:
p_id qty
ANK001 200
ANK001 50
stocks_rel:
p_id qty
ANK001 20
And i have this code right here (which was suggested by @Bohemian though i added some):
Select p.p_id, p.p_name, p.price,
`coalesce((SELECT sum(a.qty)
FROM stocks_add a GROUP BY a.p_id),0)
-coalesce((SELECT sum(r.qty)
FROM stocks_rel r GROUP BY r.p_id),0)
as stock_on_hand
from productstbl p
left join stocks_add a
on a.p_id=p.p_id
left join stocks_rel r
on r.p_id=p.p_id
GROUP BY p.p_id`
and when I execute it, it results to this:
p_id p_name price stock_on_hand
ANK001 Anklet 200 230
BRC001 Bracelet 75 230
I'm trying to get the stocks on hand from the second and third table. and link it with products table.
Suppose to be, 230 should be only with ANK001. The desired output should be this one:
`p_id p_name price stock_on_hand
ANK001 Anklet 200 230
BRC001 Bracelet 75 0
`
but the result on the first output tells the other way around. :( What's wrong with the code? please do help me.
I'm not sure what your issue with your query is, but here's how it was easiest for me to write:
select p.*, IFNULL(aqty,0) - IFNULL(rqty,0) AS stock_on_hand
from productstbl p
left join (
select p_id, SUM(IFNULL(a.qty,0)) as aqty
from stocks_add a
group by p_id
) AS asum
on asum.p_id = p.p_id
left join
(
select p_id, SUM(IFNULL(r.qty,0)) as rqty
from stocks_rel r
group by p_id
) AS rsum
on rsum.p_id = p.p_id;
and it actually gives the right results! Which is a nice thing for a query to do :)
P_ID P_NAME PRICE STOCK_ON_HAND
ANK001 ANKLET 200 230
BRC001 BRACELET 100 0