Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I have 3 tables mr, mrd, and sm. Its structure is as below
table : Mr
id | location_id | is_processed
----------------------------
1 2 0
2 2 0
3 1 0
table mrd
id | mr_id | product_id | is_generated
---------------------------------------
1 1 6 0
2 1 8 0
3 2 6 0
table sm
product_id | qty
-----------
6 40
6 80
6 50
what I need is
product_id | qty
-----------------
6 170
8 0
This is what I tried
SELECT DISTINCT(mrd.product_id),sum(sm.qty) FROM mr
JOIN mrd ON mr.id = mrd.mr_id
join sm on mrd.product_id = sm.product_id
WHERE mr.to_location = 2 AND mr.is_processed = 0 AND mrd.is_generated = 0
GROUP BY mrd.product_id, sm.qty
converted the laravel to SQL for easy
I don't think you should GROUP BY sm.qty if you're summing it... try:
SELECT DISTINCT(mrd.product_id),sum(sm.qty) FROM mr
JOIN mrd ON mr.id = mrd.mr_id
join sm on mrd.product_id = sm.product_id
WHERE mr.to_location = 2 AND mr.is_processed = 0 AND mrd.is_generated = 0
GROUP BY mrd.product_id
Also, might not even need the DISTINCT call if you're grouping by that column either:
SELECT mrd.product_id,sum(sm.qty) FROM mr
JOIN mrd ON mr.id = mrd.mr_id
join sm on mrd.product_id = sm.product_id
WHERE mr.to_location = 2 AND mr.is_processed = 0 AND mrd.is_generated = 0
GROUP BY mrd.product_id
@cwhite I tried what you said but i am getting like this
product_id | sum(sm.qty)
-----------------------
6 340.00
Can you show the query?
@cwhite Changed the name for easy understanding
Query is same
SELECT mrd.product_id, sum(sm.qty) FROM material_requests as mr
JOIN material_request_details as mrd ON mr.id = mrd.request_id
JOIN stock_masters as sm on mrd.product_id = sm.product_id
WHERE mr.to_location = 2 AND mr.is_processed = 0 AND mrd.is_generated = 0
GROUP BY mrd.product_id
If you remove the sum and group by statements, can you verify that you are actually getting all the data you expect?
Why the rows are repeating
SELECT mrd.product_id, sm.qty FROM material_requests as mr
JOIN material_request_details as mrd ON mr.id = mrd.request_id
JOIN stock_masters as sm on mrd.product_id = sm.product_id
WHERE mr.to_location = 2 AND mr.is_processed = 0 AND mrd.is_generated = 0
product_id | qty
-----------------
6 40
6 40
6 80
6 80
6 50
6 50
@anand_aks, not sure, try this to see:
SELECT mr.id as mr_id,mrd.request_id,mrd.product_id, sm.qty FROM material_requests as mr
JOIN material_request_details as mrd ON mr.id = mrd.request_id
JOIN stock_masters as sm on mrd.product_id = sm.product_id
WHERE mr.to_location = 2 AND mr.is_processed = 0 AND mrd.is_generated = 0
Or just use select * to see all rows
Please or to participate in this conversation.