Search Posts

Recursive SQL query on nested-set tree with stop condition

how to – Recursive SQL query on nested-set tree with stop condition

I am using a nested-set tree structure in a table. The concept is described here.

The example data looks as follow:

+----+-----------+------+-------+-----------------+
| id | parent_id | left | right | stop_descending |
+----+-----------+------+-------+-----------------+
|  1 |      NULL |    1 |    10 |               0 |
|  2 |         1 |    2 |     3 |               0 |
|  3 |         1 |    4 |     9 |               1 |
|  4 |         3 |    5 |     6 |               0 |
|  5 |         3 |    7 |     8 |               0 |
+----+-----------+------+-------+-----------------+

Getting the whole tree is pretty straightforward:

SELECT t0.*
FROM nested_set AS t0
LEFT JOIN nested_set AS t1 ON t0.left BETWEEN t1.left AND t1.right
WHERE t1.parent_id IS NULL
ORDER BY t0.left;

However, I would like to get all nodes whose parent has not a stop_ descending flag. The result should include nodes 1,2,3. Nodes 4,5 should be excluded as their parent has the stop_ descending flag. If nodes 4 and 5 would have children, these should be excluded as well. The recursion should stop, once the is_leaf value equals 1.

I tried many different approaches but never got the proper result. Im am running the query in MariaDB 10.1.26. Maybe there a better solution involing CTE on higher version.

Source: Stackoverflow MariaDB

Leave a Reply

Your email address will not be published. Required fields are marked *