Hierarchical Data Join of parent/child relationship in same table

I have the following table:

Id  ParentId  Weight
1   1         0
2   1         10
3   2         5

ParentId references Id of the same table. How can I query this table so that I join it on itself, adding up the cumulative weight of the third column?

For example, if I wanted to know the cumulative weight of Id 2, the result would return 15 (Id2 + Id3 = 15) as the parent of item 3 is 2. If I wanted to know the cumulative weight of item 3, it would return 5, as no records have a parent id of item 3.

Essentially, if the recor开发者_如何学编程d I am querying has a child, I want to add the sequence of data's children and return one result.

Is this possible to do in one fell swoop to the database or would I have to loop through the entire record set to find matches?

Take a look on this article. If your table is not updated frequently, you can modify a little their GenericTree procedure that it generates all paths for all rows (and call it every time you insert record into the table or update ParentId column), store this data into a new table, and then you can perform all the tasks required using simple queries. Personally, I end up with the following table structure:

CREATE TABLE `tree_for_my_table` (
`rootID` INT(11) NOT NULL, // root node id
`parentID` INT(11) NOT NULL, // current parent id
`childID` INT(11) NOT NULL, // child id (direct child of the parent)
`level` INT(11) NOT NULL, // how far child is from root
PRIMARY KEY (`rootID`, `parentID`, `childID`),
UNIQUE INDEX `childID` (`childID`, `level`)  
 )

Populating data for that table doesn't take too long even for a quite large my_table.

Last I looked, mysql didn't have a built-in way of doing hierarchical queries, but you can always use a technique such as the adjacency list, discussed (among other techniques) in Managing Hierarchical Data in MySQL, which encodes the hierarchy in another table and lets you join against that to retrieve subtrees in your hierarchy.

You need to index your tree. See Managing Hierarchical Data in MySQL for some ways to do this.