MySQL How would you do this question

Question

Here is the problem: I have a representation of a tree. let's abstract this to the fullest and say this is the tree: (id, parent_id)

the root is a row with parent_id = null

I'd like to create a table that lists all the descendants of all parents.

For example:

A Chart Description of the tree

The representation : (1,null), (2,1), (3,1), (4,2), (5,3), (6,3)

The result: (1,2), (1,3), (1,4), (1,5), (1,6), (2,4), (3,5), (3,6)

do you mean like: 1 has decendants: 2,3,4,5,6 and 3 has the decendants 5,6? — beardhatcode, Jul 10 '11 at 16:25
See this blog post: http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/ — ypercubeᵀᴹ, Jul 10 '11 at 17:25
You want to convert your **Adjacency list** to a **Transitive closure of an adjacency list**. Read Quassnoi's links in this questions for other models: http://stackoverflow.com/questions/5486144/what-is-the-best-method-to-make-unlimited-sub-catgories — ypercubeᵀᴹ, Jul 10 '11 at 17:31

score 2 · Accepted Answer · answered Jul 10 '11 at 17:03

2

Your representation is adjacency list, it's not possible to do this with one query. The fastest solution is to retrieve the whole data set and build what you need on the client side.

Check the nested set representation.

answered Jul 10 '11 at 17:03

Karoly Horvath

94,607
11
117
176

score 0 · Answer 2 · answered Jul 10 '11 at 17:31

0

Maybe here you can find a solution:

http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

answered Jul 10 '11 at 17:31

james_bond

6,778
3
28
34

MySQL How would you do this question

2 Answers2