How to substract 2 queries with multiple rows in MySQL

Question

I want to substract the results for each prod_id in this 2 queries.

SELECT prod_id, prod_name, SUM(quantity) 
FROM purchasesdetails 
LEFT JOIN products ON (products._id=purchasesdetails.prod_id) 
GROUP BY prod_id

Minus

SELECT prod_id, prod_name, SUM(quantity) 
FROM notedetails 
LEFT JOIN products 
ON(notedetails.prod_id=products._id) 
GROUP BY prod_id

So the final result is

|prod_name|SUM1-SUM2| 

prod_name is in the products table.

This is the output of the first query

prod_id | prod_name   | SUM(quantity)
176     | ANTIPIRINA  | 21
177     | BOMBAY      | 22

This is the output of the second query

prod_id | prod_name   | SUM(quantity)
176     | ANTIPIRINA  | 10
177     | BOMBAY      | 15

This is the desired output

prod_id | prod_name   | sum1-sum2
176     | ANTIPIRINA  | 11
177     | BOMBAY      | 7

Thanks!

Answer 1

SELECT prod_id, prod_name, SUM(quantity) 
FROM purchasesdetails 
LEFT JOIN products ON (products._id=purchasesdetails.prod_id) 
GROUP BY prod_id where prod_in not in SELECT prod_id 
FROM notedetails 
LEFT JOIN products 
ON(notedetails.prod_id=products._id) 
GROUP BY prod_id

U can use not in clause.