Given a self referencing table
Item
-------------
Id (pk)
ParentId (fk)
With a related table of associated values
ItemValue
-------------
ItemId (fk)
Amount
And some sample data
Item ItemValues
Id ParentId ItemId Amount
-------------------- ----------------------
1 null 1 10
2 1 3 40
3 1 3 20
4 2 4 10
5 2 5 30
6 null
7 6
8 7
I need a sproc to take Item.Id
and return the direct children with sums of all ItemValue.Amounts
for the them, their children and their children all the way down the tree.
For example, if 1
is passed in, the tree would be 2, 3, 4, 5
the direct children are 2, 3
the output would be
ItemId Amount
------------------
2 40 (values from ItemIds 4 & 5)
3 60 (values from ItemId 3)
What sort of approaches should be applied to make achieve this behavior?
I am considering using a CTE, but am wondering if there is a better/faster approach.