I have to write a query where i want to check the active elements for an item ( that item can have an alternative item and the alternative can have another alternative item and so on) .
Active will be decided based on the stocks available for the item/alternative(physicalinvent+ordered+arrived) from the inventsum table where inventsum.itemid = inventtable.itemid/inventtable.altitem
Please help me with the query as iam literally struggling with this

I tried
Select * from InventTable IT
left join inventtable IT2 on It.altitemid= IT2.itemid
left join inventtable IT3 on It2.altitemid= IT3.itemid
.
.
.
.
left join inventtable ITn+1 on Itn+1.altitemid= ITn.itemid

2nd i tried using cte

with cte as(select sum(physicalinvent+arrived+ordered) ac active,altitemid ,itemid from Inventsum
right join inventtable on inventtable .itemid = Inventsum.itemid

unionall

select sum(physicalinvent+arrived+ordered) ac active,altitemid,itemid from Inventtable ITn
join cte on cte.altitemid = ITn.itemid
left join inventsum on itn./itemid=inventsum.itemid
)