How to select all parents of a node in a hierarchical mysql table?

Question

I have a MySQL table that represents data for a tree GUI component, here's the structure of my table:

treeTable ( 
  id INT NOT NULL PRIMARY KEY, 
  parentId INT, 
  name VARCHAR(255) 
);

parentId is a self-referencing foreign key.

Now I want to write a stored procedure which gets a node id and returns a result set that contains that node and all of its parents.

For example, suppose that my table has filled with this data:

1, null, 'root'
2, 1   , 'level_1'
3, 2   , 'level_2'

Now I want to get all parent nodes of node 3 (nodes 1 and 2) and return a result set that contains all tree records. Can anybody help me please?

Answer 1

Good question. In Oracle you would use something like CONNECT BY.

Since you are using MySQL, I would suggest you change your data structure to efficiently answer that query. Here are some ideas.

Answer 2

There was a similar discussion to this that might be helpful in solving this problem.

I think I might attack this problem by recursively retrieving the data until I'd reached the root node (parent was null). I might have been inclined to do this outside of the stored procedure initially (repeatedly calling the thing until the retrieved row had the null parent), but the "closure table" solution on the page I referenced here looks like a great solution.

Answer 3

Look here under "Retrieving a Single Path". But better use a nested set approach, it will be much easier to work with a tree. Also I recommend reading "Trees In The Database - Advanced data structures" presentation.

Answer 4

There's also materialized paths to think about. Pretty simple concept that's really database agnostic. Much easier to manage inserts etc as in contrast to nested sets, you don't have to know you're left/right nodes etc before you insert.