Menu
Consider a table like this:
folders_table
-----------------------
INT id_folder
INT id_folder_parent
VARCHAR folder_name
Which stores a simple directory structure. How could I get all subdirectories of a directory with a single SELECT query?
710
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.
Alternative 1: with recursive , connect by 2+). And as of version 8.0, also MySQL supports it.
Common Table Expression (CTE) is an important feature of MySQL that is used to generate a temporary result set. It can be used with any SQL statement like SELECT, INSERT, UPDATE, etc. The complicated queries can be simplified by using CTE.
Recursion is achieved by WITH statement, in SQL jargon called Common Table Expression (CTE). It allows to name the result and reference it within other queries sometime later.
It is possible, but you need to change your database structure; once the changes are made, you can retrieve a tree of any depth in one query. The queries are slightly more complex, but it's still pretty straightforward.
173
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
