MySQL How would you do this 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_i开发者_JS百科d = null

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

For example:

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)

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.

Maybe here you can find a solution:

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