SQL help i need to find the inventory remaining in my office (i want to know unallocated asset remai-CodePudding

In sql help i have 2 tables, table one is asset table which is as follow

id	asset_code	asset_name	asset_group	asset_quantity
1	A001	demo asset	4	5
2	A002	demo asset 2	6	3

and another table is asset_allocation

id	asset_id	allocated_quantity	allocated_location
1	1	2	IT office
2	1	1	main hall

now the scenario is that i am creating an office asset management system so after allocating any asset i want to know the remaining assets are not allocated (in short i want to know the unallocated asset remaining in the office)

lets say i have 5 computers and i have allocated 3 computers so i should be remaining with 2 computers so now how do i make sql auto generate this math for me

CodePudding user response：

You must get the sum of allocations per asset. You can do this in a correlated subquery in the select clause or in a non-correlated subquery in the from clause. If you choose the latter option, outer join this result to the assets in order to keep assets that have no allocations. Use COALESCE to turn the null allocations into zero allocations so you can calculate the difference with them:

select a.*, a.asset_quantity - coalesce(aa.sum_qty, 0) as remains
from asset a
left join
(
  select asset_id, sum(allocated_quantity) as sum_qty
  from asset_allocation
  group by asset_id
) aa on aa.asset_id = a.id
order by a.id;

CodePudding user response：

try like below using left join

select a.* from asset a left join asset_allocation al on a.id=al.asset_id
where al.asset_id is null