Hi,@Analyst_SQL
You misunderstood what I meant , I asked you to check the value of #tbl_CONTD after the connection, according to your thinking, QTY must be 2.Try this:
;with cte_D as(
select F.Level_Four_Name,I.Codeitem,I.Descriptionitem,sum(D.C_Weight)Weight,sum(D.qty)Qty ,C.Cat_name
from #ItemMasterFile I inner join #tbl_ContD D on D.Codeitem=I.Codeitem
left join #Containerno con on con.CID=d.cid
full outer join #tbl_Account_L_Four F on F.Level_Four_ID=con.Supplier_ID
left join #Concategory c on c.Cat_ID=d.cat_ID
group by F.Level_Four_Name,i.Codeitem,i.Descriptionitem,c.Cat_name
union all
select ' 'as Level_Four_Name,I.Codeitem,I.Descriptionitem,sum(p.Weigth)Weight,sum(P.prdqty)Qty ,C.Cat_name
from #ItemMasterFile I inner join #Probale P on P.Codeitem=I.Codeitem
left join #Concategory c on c.Cat_ID=P.cat_ID
group by i.Codeitem,i.Descriptionitem,c.Cat_name
)
select Level_Four_Name, isnull(null,Descriptionitem)Descriptionitem,
isnull(null,Qty)QTY,
isnull(null,Weight)Weight,isnull(null,Cat_name)cat_name
from cte_D
Bert zhou