#MLPRoducts has a list of productID's that I need to get inventory for.
ParentChild has parent/child relationship (see below)
product_id_parent, product_id, priority
123					431			1
123					439			2
39					201			1
48					128			1
48					129			2
48					130			3
........

The tables SA(all location) has inventory for products (see below)

Product_id	LocaationID		onHandQty		transfer
431			41				12				1
431			23				5				0
130			41				21				39
129			78				8				2
130			38				27				0
.........

The tables S1(main location) has inventory for products (see below)

Product_id	onHandQty		transfer
431			12				1
431			5				0
130			21				39
129			8				2
130			27				0
.........


this query below gets me all child sku's from #MLPRoducts, grabs all other child from their parents (siblings). now I need to sum up the
quantities from all location by each products and show 2 columns. 1 for all locations combined from table SA, the other for the main location from table S1
I attempted it but commented out as it's not working.

select
	  PC.product_id_parent
	, PC.product_id
	, PC.Priority
--	, SUM(S1.OnHand_ForReplenishment) as DC
--	, SUM(SA.OnHand) as AllStores
	from ParentChild PC
--inner join LocAll SA on SA.Product_id = PC.product_id
--inner join Loc1 S1 on S1.Product_id = PC.product_id

where product_id_parent in (
select product_id_parent from ParentChild where product_id in (select product_id from #MLPRoducts))

group by PC.product_id_parent, PC.Priority, PC.product_id
--, S1.OnHand_ForReplenishment, SA.OnHand