Menu
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?
233
level + 1 FROM pc a JOIN cte c ON a. parent = c. child ) SELECT distinct parent, child , level FROM cte order by level, parent; This will give you all descendants and the level.
A parent-child relationship between two tables can be created only when there is a PRIMARY KEY in one table and FOREIGN KEY in another table. Here is an example of SQL join three tables with conditions.
MySQL (still) doesn't support hierarchical queries (as other modern DBMS do). You will need to write a stored procedure or use a different datamodel. Possible duplicate of What are the options for storing hierarchical data in a relational database?
A recursive CTE is a subquery which refer to itself using its own name. The recursive CTEs are defined using WITH RECURSIVE clause. There should be a terminating condition to recursive CTE. The recursive CTEs are used for series generation and traversal of hierarchical or tree-structured data.
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.
194
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
