CODEWITHSUNDEEP
sqlsql-serversql-server-2012inner-join
techV
techV

Reputation: 935

using sql join on three tables

I have 3 tables which maintains stock entries for each products/items. These three tables like below :

Table : ItemStock (to maintain remaining stock of each item)

Id  ItemId  OpgQty  BranchID  CurrentStock
1     7       0       1            8
2     7       0       2            3
3     6       0       1            2
4     6       0       2            0

Table : ItemPurchase (StockIn)

    Id  ItemId   Qty  BranchID  
    1     7       5       1            
    2     7       4       2   
    3     7       6       1  
    4     7       2       2          
    5     6       4       1           
    6     6       2       2           
    7     6       2       1

Table : ItemSale (StockOut)

    Id  ItemId   Qty  BranchID  
    1     7       2       1            
    2     7       3       2   
    3     7       1       1  
    4     6       4       1           
    5     6       2       2

Desired Output (based on sql queries)
I want to have result like below : (part of report)

Id ItemId  OpgQty  BranchId StockIn  StockOut  CurrentStock
1    7       0        1        11       3           8
2    7       0        2        6        3           3
3    6       0        1        6        4           2
4    6       0        2        2        2           0

I was trying to get the desired result but was not able to do so. Please help!!!

Upvotes: 0

Views: 83

Answers (4)

Marc Guillot
Marc Guillot

Reputation: 6455

A very simple query that gives the desired result is :

select *,
       (select sum(Qty) 
        from ItemPurchase 
        where ItemPurchase.ItemId = ItemStock.ItemId and 
              ItemPurchase.BranchId = ItemStock.BranchId) as StockIn,
       (select sum(Qty) 
        from ItemSale 
        where ItemSale.ItemId = ItemStock.ItemId and 
              ItemSale.BranchId = ItemStock.BranchId) as StockOut
from ItemStock

Upvotes: 1

Jacky Montevirgen
Jacky Montevirgen

Reputation: 317

Please Try This ... I hope you consider this too.

Upvotes: 0

Blank
Blank

Reputation: 12378

Two subqueries with group by and aggregation will get what you want.

select 
    s.*, 
    coalesce([ip].StockIn, 0) as StockIn,  -- In case of no records in ItemPurchase or ItemSale, coalesce is neccessary.
    coalesce([is].StockOut, 0) as StockOut
from ItemStock s
left join (
    select sum(Qty) as StockIn, ItemId, BranchId
    from ItemPurchase
    group by ItemId, BranchId
) [ip] on s.ItemId = [ip].ItemId and s.BranchId = [ip].BranchId
left join (
    select sum(Qty) as StockOut, ItemId, BranchId
    from ItemSale 
    group by ItemId, BranchId
) [is] on s.ItemId = [is].ItemId and s.BranchId = [is].BranchId

See demo in sqlfiddle.

Upvotes: 0

CompEng
CompEng

Reputation: 7376

try this;

select
m.Id,
m.ItemId,
m.OpgQty,
m.BranchID,
si.StockIn,
m.CurrentStock-si.StockIn StockOut,
m.CurrentStock
from
ItemStock m
inner join
(
select 
ItemId,BranchId,sum(Qty) as StockIn
from
ItemPurchase
group by ItemId,BranchId
) si on si.ItemId=m.ItemId and si.BranchId=m.BranchId

Upvotes: 1

Related Questions