I want to display all item from tbl_Bottles ,below i tried but not getting expected result.
Create table #tbl_bottles (B_ID int,B_Name varchar(50))
Create table #tbl_GRN (G_ID int,B_ID int,G_Qty int)
Create table #tbl_Issue (I_ID int,B_ID int,I_Qty int)
insert into #tbl_bottles VALUES(1,'19Liter')
insert into #tbl_bottles VALUES(2,'6Liter')
insert into #tbl_bottles VALUES(3,'2Liter')
insert into #tbl_bottles VALUES(4,'Nozzel')
insert into #tbl_bottles VALUES(5,'Cap')
insert into #tbl_bottles VALUES(6,'Stand')
insert into #tbl_bottles VALUES(7,'Pump')
insert into #tbl_bottles VALUES(8,'Pip')
insert into #tbl_bottles VALUES(9,'Blue Stand')
insert into #tbl_GRN VALUES(1,1,5)
insert into #tbl_GRN VALUES(2,1 ,8)
insert into #tbl_GRN VALUES(3,3 ,12)
insert into #tbl_GRN VALUES(4,5 ,6)
insert into #tbl_Issue VALUES(1,1,5)
insert into #tbl_Issue VALUES(1,1,2)
insert into #tbl_Issue VALUES(2,3 ,8)
;With Cte1 as (
Select B_ID as Code,B_Name as Item from #tbl_bottles
), cte2
as
(
select B_ID as Code ,Sum(isnull(G_QTY,0)) as GQty from #tbl_GRN
group by B_ID
)
,cte3 as
(select B_ID as Code ,Sum(isnull(I_QTY,0)) as IQty from #tbl_issue
group by B_ID)
select Item , isnull(GQty-IQty,0)Qty from Cte1 join cte2 on cte1.Code=cte2.Code
full join cte3 on Cte2.Code=cte3.Code
