Aan Aan - 2 months ago 22
SQL Question

Form a table from two tables with quantity manipulation

I have two tables: item & job_item.
The table item has

item_id
as primary key, while job_item has a composite key (
item_id
&
job_id
).

enter image description here
enter image description here

I want to to get a a result like:

enter image description here

Where the
remaining_quantity
is:
item.quantity – sum(job_item.quantity)
, and if this equals zero I don’t want to show this specific item_id record.
My try:

SELECT `item`.`item_id` , `item_name` , `item_brand` , `item`.`quantity` - sum( `job_item`.quantity` ) AS remaining_quantity
FROM `item` , `job_item`
WHERE `job_item`.`item_id` = `item`.`item_id`;


But this shows wrong results and only always one record.

Answer

Since you have unique item_id in your item table you just need to group by columns from item table and add a having clause to discard results with remaining_quantity = 0:

select
  i.item_id, i.item_name, i.item_brand, 
  i.quantity - sum(ji.quantity) as remaining_quantity
from item i
join job_item ji on i.item_id = ji.item_id
group by i.item_id, i.item_name, i.item_brand, i.quantity 
having i.quantity - sum(ji.quantity) <> 0

Result:

 item_id | item_name  | item_brand  | remaining_quantity
---------+------------+-------------+-------------------
      22 | dummy name | dummy brand |                39

Note:

  • please use explicit JOIN syntax, don't do it the old-fashioned way in WHERE clause

Edit per request in comments:

If you need to deal with case where there is no matching row in job_item table, then change the join to a LEFT JOIN and apply coalesce over the sum so that it will return 0 instead of NULL value:

select
  i.item_id, i.item_name, i.item_brand,
  i.quantity - coalesce(sum(ji.quantity),0) as remaining_quantity
from item i
left join job_item ji on i.item_id = ji.item_id
group by i.item_id, i.item_name, i.item_brand, i.quantity
having i.quantity - coalesce(sum(ji.quantity),0) <> 0