Recursive CTE SQL: Preventing Recursive Loop with Multiple Recursive References
Question
I have a recursive CTE query, but it doesn't work when the loop is created. I've already fixed simple loops (e.g. 1 → 2 → 1), but can't fix more complex loops (e.g. 1 → 2 → 3 → 2).
Request details
The test table has two columns: Base and Parent. I want a list with all ancestors.
My query works with the example data below if you start with test2, but not when you run in test1.
Sample data
Base Parent
---- ------
test1 test2
test2 test3
test3 test2
SQL Query (my fix is corrected in comments)
;with sample_data (Base, Parent) as (
select 'test1', 'test2'
union select 'test2', 'test3'
union select 'test3', 'test2'
),
nt_list (Base, Ancestor, [level]) as (
select Base,
Parent Ancestor,
1 [level]
from sample_data
where Base = 'test1' -- START HERE
union all
select ntl.Base,
nt.Parent,
ntl.[level] + 1 [level]
from nt_list ntl
join sample_data nt on ntl.Ancestor = nt.Base
where nt.Parent <> ntl.Base -- fix recursive bug (e.g. 1 -> 2 -> 1)
-- WHAT I TRIED TO ADD BUT CANNOT: (e.g. 1 -> 2 -> 3 -> 2)
and nt.Parent in (select Ancestor from nt_list)
)
select distinct
ntl.Base,
ntl.Ancestor
from nt_list ntl
order by Ancestor
SQL error: recursive member of common table expression "nt_list" has multiple recursive references.
+3
source to share
4 answers
The final version. Assuming it '/'
will never be part of the base or parent name.
;with sample_data (Base, Parent) as (
-- TEST 1
-- select 'test1', 'test2'
--union select 'test2', 'test3'
--union select 'test3', 'test2'
-- TEST 2
select 'test1', 'test2'
union select 'test2', 'test3'
union select 'test3', 'test4'
union select 'test3', 'test9'
union select 'test4', 'test5'
union select 'test5', 'test3'
union select 'test9', 'test8'
-- TEST 3
-- select 'test1', 'test2'
--union select 'test2', 'test3'
--union select 'test3', 'test1'
-- TEST 4
-- select 'test1', 'test1'
--union select 'test1', 'test2'
),
nt_list (Base, Ancestor, [level], [path]) as (
select Base,
Parent Ancestor,
1 [level],
'/' + convert(varchar(max), rtrim(Base)) + '/' [path]
from sample_data
where Base = 'test1' -- START HERE
union all
select ntl.Base,
nt.Parent,
ntl.[level] + 1 [level],
ntl.[path] + rtrim(nt.Base) + '/'
from nt_list ntl
join sample_data nt on ntl.Ancestor = nt.Base
where ntl.path not like '%/' + rtrim(nt.Parent) + '/%'
)
select distinct
ntl.Base,
ntl.Ancestor
from nt_list ntl
order by Ancestor
+2
source to share
you can use
;WITH nt_list (Base, Ancestor, [level], cycle, path)
AS (SELECT Base,
Parent Ancestor,
1 [level],
0 AS cycle,
CAST('.' AS VARCHAR(max)) + ISNULL(Parent, '') + '.' + Base + '.' AS [path]
FROM NoteTest
WHERE Base = 'test1'
UNION ALL
SELECT ntl.Base,
nt.Parent,
ntl.[level] + 1 [level],
CASE
WHEN ntl.[path] LIKE '%.' + LTRIM(nt.Base) + '.%' THEN 1
ELSE 0
END AS cycle,
ntl.[path] + LTRIM(nt.Base) + '.' AS [path]
FROM nt_list ntl
JOIN NoteTest nt
ON ntl.Ancestor = nt.Base
AND ntl.cycle = 0)
SELECT ntl.Base,
ntl.Ancestor
FROM nt_list ntl
ORDER BY Ancestor
+1
source to share
This case is ideal, but like other items, for example:
select 'test1', 'test2'
union select 'test2', 'test3'
union select 'test3', 'test4'
union select 'test4', 'test5'
union select 'test10', 'test11'
union select 'test11', 'test30'
...
The result should be:
test1 test1 <- adding this
test1 test2
test1 test3
test1 test4
test1 test5
test10 test10 <- adding this to multiple bases
test10 test11
test10 test30
0
source to share