Dynamic tree query

Anon

Hi!

I have table like this, which makes dynamic tree.

Id Parent Cost
1 0 10
2 1 20

3 1 30
4 2 40

Now I would like to calculate for example the total cost of the id 2 (20+40=60). How to make the sql statement, which aggregate all the cost of selected id and also from the child objects ? How about grouping by the level of parent?

-Petri-

tomhath

select sum(cost) where Id=2 or Parent=2

Anon

Thans for your quick answer,

That works if I have only 2 levels in tree, but how about if I have 3 or 4 levels in tree? I guess I have to do recursive aggregation query, but how?

-Petri-

tomhath wrote:

select sum(cost) where Id=2 or Parent=2