SQL: Select SUM of all children records recursively

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: Numbe开发者_如何学Cr 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)

---

This may be some some assistance: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

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.

---

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

---

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