SQL: Select SUM of all children records recursively

Question

I have a table that has a one to many relationship with itself. Each record can have n number of children from that same table. For example

create table folder
ID: Number 20 PK
PARENT_ID: Number 20 FK references folder.ID
SIZE: NUMBER 20
...

Given an ID, I want to select the SUM(SIZE) of all folder records recursively. The target database is MySql 5, but it would be nice if it was generic enough to work in Oracle and MS-SQL as well.

I won't know how deep the tree is, could be 1 level, could be 50 (or more)

Answer 1

One solution would be to add a column to the table "topmost_parent" and join on that.

Answer 2

This may be some some assistance: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

This would be a simple query in Oracle ( http://download-east.oracle.com/docs/cd/B12037_01/server.101/b10759/queries003.htm) since it supports hierarchical queries using "CONNECT BY" but I don't think there's a comparable solution for MySQL. It looks like you're going to do something really inefficient or you're going to have to modify your table structure to support this specific function.

Answer 3

You should consider re-structuring the data using a nested set model. The following link describes how to do it:

http://www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql