5

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.

Peet Brits
  • 2,911
  • 1
  • 31
  • 47

4 Answers4

3

The final version. Assuming '/' 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
Peet Brits
  • 2,911
  • 1
  • 31
  • 47
2

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 
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Your script does not work for a chain longer than 3 (e.g. try 1->2->3->4), but I see what you did with the path and can fix it for myself. – Peet Brits Feb 19 '13 at 06:58
  • Also, there is no need for `cycle`. A good `like` is enough. I posted the final version. – Peet Brits Feb 19 '13 at 07:41
0

My current workaround is to add a level limit (adding and ntl.[level] <= 100 to the query) and let the select distinct remove the duplicate entries.

Peet Brits
  • 2,911
  • 1
  • 31
  • 47
0

This case is perfect, but as you would for more items like:

    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
Juliano
  • 1
  • 1
  • The `where` query with the comment "START HERE" defines where the query starts. Update it to something like `where Base not in (select Parent from sample_data)` to include all items (note that this will exclude loops, but it will work for your example). You may also want to change the `order by` at the very end to `order by Base, Ancestor`. – Peet Brits Jul 08 '13 at 13:37
  • In other words, you must tell the query where it should start. – Peet Brits Jul 08 '13 at 13:43