SQL Subtract from different tables

I’m subtracting values from diferent tables based on ID.

Its like an inventory where I’ve the tables for Itens In and Itens Out.

What I want to do is to substract the Itens In – Itens Out based on ID of the iten.

I manage to do that, but if an Iten only as an In movement, the query just shows an empty row, when it should show the In movement – Out moment that even if it doesnt exists should be considered as 0, showing in this case only the value of the IN movment.

Can someone help?

Each row in each table represents one item.

TABLE – in_used

id_item_____qnt
1 _________500
2 _________1000

TABLE – out_used

id_item_____qnt
1 _________200

OUTPUT EXPECTED

used_stock

id_item____qnt
1 ________300
2 ________1000 (there’s no out movement so it should show only the IN one)

Select
    in_used.qnt - out_used.qnt As used_Stock
From
    in_used Inner Join
    out_used On in_used.id_item = out_used.id_item

Answer

If id_item is unique in in_used and there is at most 1 row in out_used then all you need is a LEFT join and COALESCE():

select i.id_item, 
       i.qnt - coalesce(o.qnt, 0) used_Stock
from in_used i left join out_used o
on i.id_item = o.id_item

If there are more rows use also aggregation:

select i.id_item, 
       sum(i.qnt) - coalesce(sum(o.qnt), 0) used_Stock
from in_used i left join out_used o
on i.id_item = o.id_item
group by i.id_item

Advertisement

Answer