How to calculate available quantity from SQL joins

Question

I am out of luck with a query with joins in a Postgres database.

I have to find out the available quantity with the given situation.

I have 4 tables (allocate, orders, orderitems, material):

• the allocate table has 3 columns cntr_id as contractor, matid as material, qty_given

• the orderitems table has matid, qty_used, orderid

• the orders table has cntr_id, orderid

My query is to find available stock with each contractor.

I thought it would be very easy to join tables to get the desired result but its not that easy.

I have tried many combinations of joins but to no gain and thus I need your help.

Let's see an example - my allocate table looks like this

select cntr_id, matid, qty_given 
from allocate

result of above query

This query returns a list of all the contractors, matid and qty_given.

The query here returns cntr_id, matid and qty_used:

select 
    o.cntr_id, i.matid, i.untsreq_perorder used
from
    orderitems i 
join 
    orders o on i.orderid = o.orderid
join 
    material m on m.matid = i.matid
group by 
    o.cntr_id, i.untsreq_perorder, i.matid

result of above query with joins

This query returns a list of only those values which are used in order table which is fine. With this query I can find all the contractor and qty_used by them.

Now the main issue is how to use this second query to get available stock with contractors in the allocated table.

I have used several combinations of joins but without any success.

What I am trying to join is

select 
    a.cntr_id, a.matid, x.used
from
    allocate a
left join 
    (select o.cntr_id, i.matid, i.untsreq_perorder used
     from orderitems i 
     join orders o on i.orderid = o.orderid
     join material m on m.matid = i.matid
     group by o.cntr_id, i.untsreq_perorder, i.matid) x on a.cntr_id = x.cntr_id
group by 
    a.cntr_id, a.matid, x.used

The result is like this:

result of above query

Please help me find the approriate query to get available quantity with contractors.

I am extremely confused

Answer 1

thanks for the support, by the way i have figured out the corret way to fetch the desired result.

select 
  a.matid, 
  a.cntr_id, 
  a.qty_given, 
  coalesce(sum(i.untsreq_perorder), 0) used, 
  coalesce((a.qty_given - i.untsreq_perorder),0) as available 
from 
  allocate a 
  left join orders o on o.cntr_id = a.cntr_id 
  left join material m on m.matid = a.matid 
  left join orderitems i on i.orderid = o.orderid 
  and i.matid = m.matid 
group by 
  a.matid, 
  a.cntr_id, 
  a.qty_given, 
  i.untsreq_perorder 
order by 
  a.cntr_id desc

the main logic which saved my life was the use of

left join orderitems i on i.orderid = o.orderid 
      and i.matid = m.matid 

you can have a full view on

https://www.db-fiddle.com/f/vsCsRLEDr6Vu582xwwv3f4/1

thanks.