patternsqlModerate
Recursive self joins
Viewed 0 times
recursivejoinsself
Problem
I have a
where
Now, how can I
The comments might be several levels down...
comments table, which can be simplified down to this:comments
=======
id
user_id
text
parent_idwhere
parent_id is nullable, but might be a key for its parent comment.Now, how can I
select all descendants of a specific comment?The comments might be several levels down...
Solution
Hierarchical queries, as those recursive queries are known, are now supported in MySQL 8.
Old Answer
Alternatively, you can find a dynamic (and thus, potentially dangerous) trick here: https://stackoverflow.com/questions/8104187/mysql-hierarchical-queries
You can also find a discussion on how to store hierarchical data with other models than with an Adjacency List (i.e. the Parent column) here: https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/
Good luck!
Old Answer
Alternatively, you can find a dynamic (and thus, potentially dangerous) trick here: https://stackoverflow.com/questions/8104187/mysql-hierarchical-queries
You can also find a discussion on how to store hierarchical data with other models than with an Adjacency List (i.e. the Parent column) here: https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/
Good luck!
Context
StackExchange Database Administrators Q#46127, answer score: 17
Revisions (0)
No revisions yet.