Suppose we perform a recursive query to obtain the children of a certain tree node via an adjacency table of the tree, but it suffices to obtain only a single sub-tree.
As an example, let's create the adjacency table of the tree as
CREATE TABLE Tree
(parent INTEGER,
child INTEGER);
INSERT INTO Tree
VALUES -- (parent -> child)
(1, 2), (1, 3), (1, 4),
(2, 5), (2, 11), (3, 9),
(5, 6), (5, 7), (5, 8),
(9, 10), (11, 12);
and then make a recursive query to obtain the children of node 2:
WITH RECURSIVE children_i (parent, child)
AS (
-- anchor/initial values
VALUES (NULL, 2)
-- SELECT parent, child FROM Tree WHERE parent = 2 LIMIT 1
UNION ALL
-- recursion
SELECT children_i.child, Tree.child FROM Tree, children_i
WHERE Tree.parent = children_i.child
)
SELECT * FROM children_i;
which will produce
|2
2|5
2|11
5|6
5|7
5|8
11|12
Now how could we restrict the query above to follow only a single sub-tree (say only 2->5->{6, 7, 8} and not 2->11)?
I have tried to add a LIMIT to the anchor part of the recursion,
WITH RECURSIVE children_i (parent, child)
AS (
-- anchor/initial values
SELECT parent, child FROM Tree WHERE parent = 2 LIMIT 1
UNION ALL
-- recursion
SELECT children_i.child, Tree.child FROM Tree, children_i
WHERE Tree.parent = children_i.child
)
SELECT * FROM children_i;
yet it yields a syntax error, LIMIT clause should come after UNION ALL not before (SQLite 3.16.2).
How could one achieve this in SQLite?
You could use LIMIT but you need to extract it to separate cte:
WITH anchor AS (
SELECT parent, child
FROM tree
WHERE parent = 2
-- ORDER BY ...
LIMIT 1
), children_i(parent,child) AS (
-- anchor/initial values
SELECT parent, child
FROM anchor
UNION ALL
-- recursion
SELECT c1.child, t1.child
FROM tree t1
JOIN children_i c1
ON t1.parent = c1.child
)
SELECT * FROM children_i;