Question
I have a recursive CTE query, but it fails when a loop is created. I already fixed simple loops (e.g. 1 -> 2 -> 1), but cannot fix more complex loops (e.g. 1 -> 2 -> 3 -> 2).
Query Detail
The test table has two columns: Base and Parent. I want a list with all ancestors.
My query works on the sample data below if you start at test2, but not when you start at test1.
Sample Data
Base Parent
---- ------
test1 test2
test2 test3
test3 test2
SQL Query (my attempted fix is marked 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 a common table expression 'nt_list' has multiple recursive references.