0

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 record 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?

Improve this question

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

Comments

0

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.