How to find the differences between the values of a specific column based on a key?

Question

I've two tables as purchase_details and invoice_details

and i want to store the inventory/stock of each product from the data of these two tables.

structure of purchase_details.

'pid', 'int(10)'
'product_id', 'int(10)' 
'quantity', 'float(8,2)'
'amount', 'float(12,2)'
'expiry_date', 'date'

structure of invoice_details.

'invoice_id', 'int(10) unsigned'
'product_id', 'int(10) unsigned'
'quantity', 'float(10,2)'
'price', 'float(12,2)'

i want to calculate the total quantity of remaining stock (quantity of sum of products from purchase_details) - (quantity of sum of products from invoice_details)

Product_id is would be same for the two tables.

Product table consists of product data, and the structure is

'id', 'int(10) unsigned'
'cid', 'int(10) unsigned'
'name', 'varchar(90)'
'selling_price', 'float(10,2)'
'mrp', 'float(10,2)'
'reorder_level', 'bigint(20) unsigned'

the invoice_details may or may not have entries for every product_id.

how can i proceed?

Answer 1

This one is my new answer, already tested on sqlserver. I am not sure about mysql

select pro.id, SUM(pro.quantity - inv.quantity)
from (select sum(p.quantity) as quantity, p.id as id from product p group by p.id) as pro, 
     (select sum(i.quantity) as quantity, i.id as id from invoice i group by i.id) as inv
where inv.id = pro.id
group by pro.id;

Answer 2

select  ( sum(purchase_details.quantity) - sum(invoice_details.quantity)) as stock  
    from products 
    left outer join purchase_details 
    on products.product_id == purchase_details.product_id
    left outer join invoice_details  
    on  products.product_id  =  invoice_details.product_id 
    group by products.product_id

this will give you the stock for each product and you can add your where statement if you need certain products

select  ( sum(purchase_details.quantity) - sum(invoice_details.quantity)) as stock  
    from products 
    left outer join purchase_details 
    on products.product_id == purchase_details.product_id
    left outer join invoice_details  
    on  products.product_id  =  invoice_details.product_id 
    where products.product_id = 137
    group by products.product_id