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.**