CODEWITHSUNDEEP
phpmysqlsql
laradev
laradev

Reputation: 918

mysql - join one table with two other tables

I have 3 tables

Products
[id, name]

Purchases
[id, product_id, quantity, date]

Sales
[id, product_id, quantity, date]

I want to list all the product with remaining quantity for each product.

I have tried with the following query, but it is not working.

SELECT pr.id, pr.name, (sum(pu.quantity))-sum(s.quantity) as quantity 
FROM `products` pr 
join purchase pu ON pu.product_id = pr.id
left join sales s ON s.product_id = pr.id
GROUP BY pr.id

I know that the above query creates duplicate rows for purchase and sales since we are joining both the table with products.

Please help me to write the correct query to find the remaining product quantity

Upvotes: 1

Views: 101

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You need to aggregate before the join. One method is explicitly in the from clause:

SELECT pr.id, pr.name,
       (coalesce(pu.sumq, 0) - coalesce(s.sumq, 0)) as quantity 
FROM `products` pr left join
     (SELECT pu.product_id, SUM(pu.quantity) as sumq
      FROM purchase pu
      GROUP BY pu.product_id
     ) pu
     ON pu.product_id = pr.id left join
     (SELECT s.product_id, SUM(s.quantity) as sumq
      FROM sales s 
      GROUP BY s.product_id
     ) s
     ON s.product_id = pr.id
GROUP BY pr.id, pr.name;

Upvotes: 1

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

Use IFNULL instead. Because 12 + null = null

SELECT pr.id, pr.name, 
sum(ifnull(pu.quantity,0))-sum(ifnull(s.quantity,0)) as quantity 
FROM `products` pr 
left join purchase pu ON pu.product_id = pr.id
left join sales s ON s.product_id = pr.id
GROUP BY pr.id,pr.name

And since you add pr.name in resultset, you also need to put it in GROUP BY clause.

Upvotes: 0

Related Questions