How to select all parents of a node in a hierarchical mysql table?
- by Ehsan Khodarahmi
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?
