Add and display all values-CodePudding

There are 2 tables called Item & Items_in_issue_Note(Items_in_issue_Note is a many to many table).

In Item table there is a column called Available_Qty and in Items_in_issue_Note there is a column called Issued_Qty.

I want to get the sum of Issued_Qty for each item and add it to Available_Qty in each item and display them item wise to get the Quantity before issue items.

I know how to get the sum of Issued_Qty by using

select ItemCode, sum(Issued_Qty) 
from Items_In_Issue_Note 
group by ItemCode

and how to get the Available_Qty by using

select itemCode,Available_Qty 
from Item

and know how to get the quantity per item by using

select 
    itemCode,
    Available_Qty   (select sum(Issued_Qty)
                     from Items_In_Issue_Note 
                     where ItemCode='I001') 
from Item 
where ItemCode='I001'

But want to know how to get the output for all the items.

Thank you.

CodePudding user response：

By using group by and join you may get the desired result. Try the following

select 
  A.itemCode,
  A.Available_Qty   Coalesce(sum(B.Issued_Qty),0) 
from Item A left join 
Items_In_Issue_Note B on A.ItemCode=B.ItemCode
group by A.itemCode,A.Available_Qty

Your code modified below one is modified based on sub-query depends on main table value

select 
itemCode,
Available_Qty   (select sum(Issued_Qty)
                 from Items_In_Issue_Note 
                 where ItemCode=Item.ItemCode) 
from Item

Best way is follow join instead of sub-query because sub-query gives performance issue.