exclude self-referencing elements recursive cte

Question

I am creating an recursive CTE because I need to make an hierachy of combinations.

I have a problem when I have FROM referencing back to TO. Then it endless loops and hits the maxrecursion limits. I could set a where clause on Levels, but then i might not end up with the correct data because my tree can be x levels deeps

My data could look like this

From    To      Total  Type
98579   10406   82     B
98579   17005   5834   S    
98579   18879   6323   S    
98579   18889   215    S
10406   43594   234    B
10406   73959   10     B
10406   98579   22824  B    
43594   83827   4      S
43594   38475   543    S

As you can see in data 98579 hits 10406 and vise verca.

My cte looks like this now:

    ;WITH x AS
(
    -- anchor:
  SELECT b.[From]
      ,b.[To]
      ,b.[Total]
      ,b.[Type]
      ,0 as levels
  FROM [dbo].[Test] b

  where b.[FROM]= 98579
    UNION ALL
    -- recursive:
    SELECT tm.[FROM], tm.[TO], tm.[Total], tm.[Type],levels +1
    FROM  [dbo].[Test] AS tm INNER JOIN  x
    ON x.TO= tm.FROM
    --where levels <= 1
)
SELECT  *FROM x
order by levels

What can i do?

Seems like the real problem is the data; why do you have circular references in the first place? — Thom A, Nov 23 '20 at 14:31
Thats how the business works. Its movement where you can move from one place to another but you can also move them back. — SqlKindaGuy, Nov 23 '20 at 14:33
If that's the case, I would personally expect an acsending ID, Sequence, or effective date so you can tell where something starts. I assume you have one then? If so, just ensure that the ID is greater than the "current" row's. — Thom A, Nov 23 '20 at 14:37
It would already exist in your table; if it doesn't how do you tell where to "start" of a circular reference is? — Thom A, Nov 23 '20 at 14:40
Nope there is no seq id. :/ but i can create one by ranking. — SqlKindaGuy, Nov 23 '20 at 14:41
Ranking on what? If th column doesn't exist how can you create it accurately? — Thom A, Nov 23 '20 at 14:42
Good question. There is no timelimit in this. I just need to know if A moved something to B, C or D and if B or C or D moved anything to another. Based on the Type = B and of cause without circular reference. — SqlKindaGuy, Nov 23 '20 at 14:44
Example a person has been from London to Oxford. They have been from Manchester to Newcastle, they have been from Brighton to London, as well as Oxford to Manchester and Newcastle to Brighton. Where did they start? How do you answer th question without additional information? (You can't) — Thom A, Nov 23 '20 at 14:44
Im not sure I quite follow. Why is a rankingID not sufficient? — SqlKindaGuy, Nov 23 '20 at 14:54
So, again, where is `RankingID` when you have nothing to rank the data on? — Thom A, Nov 23 '20 at 15:11
True :) So theres really nothing to do? Ive read this https://www.sqlservercentral.com/articles/common-table-expressions-and-circular-references and here he needs to determine the "the legitimate" data and then he hardcores a stop value Levels < X — SqlKindaGuy, Nov 23 '20 at 15:15
[This](https://stackoverflow.com/a/42139978/92546) answer demonstrates one way of terminating cycles in data while using a recursive CTE. It involves keeping track of the path already traversed during recursion for each row and terminating branches that revisit a row. — HABO, Nov 23 '20 at 15:15

Tim Mylott · Accepted Answer · 2020-11-23T16:07:13.950

If I understand correctly you just want to terminated the recursion once it has come back to where it started from.

One option would be to add a column like StartPoint or whatever you want to call it and then use that in the where clause to terminate the recursion or filter those out.

Without knowing specifically what your desired output is, I made the assumption this was what you were after based on the sample data, comments added in code:

DECLARE @TestData TABLE
    (
        [From] INT
      , [To] INT
      , [Total] INT
      , [type] CHAR(1)
    );

INSERT INTO @TestData (
                          [From]
                        , [To]
                        , [Total]
                        , [type]
                      )
VALUES ( 98579, 10406, 82, 'B' ) , ( 98579, 17005, 5834, 'S' ) , ( 98579, 18879, 6323, 'S' ) , ( 98579, 18889, 215, 'S' ) , ( 10406, 43594, 234, 'B' ) , ( 10406, 73959, 10, 'B' ) , ( 10406, 98579, 22824, 'B' ) , ( 43594, 83827, 4, 'S' ) , ( 43594, 38475, 543, 'S' );

WITH [x]
AS (
   -- anchor:
   SELECT [b].[From] AS [StartPoint] --Where are we starting
        , [b].[From]
        , [b].[To]
        , [b].[Total]
        , [b].[type]
        , 0 AS [levels]
   FROM   @TestData [b]
   WHERE  [b].[From] = 98579
   UNION ALL
   -- recursive:
   SELECT     [x].[StartPoint] --Add it here
            , [tm].[From]
            , [tm].[To]
            , [tm].[Total]
            , [tm].[type]
            , [x].[levels] + 1
   FROM       @TestData AS [tm]
   INNER JOIN [x]
       ON [x].[To] = [tm].[From]
   WHERE      [x].[StartPoint] <> [tm].[From] --stop the recursion once we have come back to where it started, filter those out.
   )
SELECT   [x].[From]
        , [x].[To]
        , [x].[Total]
        , [x].[type]
        , [x].[levels]
FROM     [x]
ORDER BY [x].[levels];

Giving results:

From        To          Total       type levels
----------- ----------- ----------- ---- -----------
98579       10406       82          B    0
98579       17005       5834        S    0
98579       18879       6323        S    0
98579       18889       215         S    0
10406       43594       234         B    1
10406       73959       10          B    1
10406       98579       22824       B    1
43594       83827       4           S    2
43594       38475       543         S    2

In this example I included where you added the filter WHERE [b].[From] = 98579 which wasn't clear if that was to show the example of the circular reference or you are doing that to indicated your starting point.

If you remove that where clause in the above code it will give all of it. Basically each row is consider the StartPoint and you will get all recurrences for each of those rows, but will stop/filter out once it has come back to where it started:

Giving you:

From        To          Total       type levels
----------- ----------- ----------- ---- -----------
98579       10406       82          B    0
98579       17005       5834        S    0
98579       18879       6323        S    0
98579       18889       215         S    0
10406       43594       234         B    0
10406       73959       10          B    0
10406       98579       22824       B    0
43594       83827       4           S    0
43594       38475       543         S    0
98579       10406       82          B    1
98579       17005       5834        S    1
98579       18879       6323        S    1
98579       18889       215         S    1
43594       83827       4           S    1
43594       38475       543         S    1
10406       43594       234         B    1
10406       73959       10          B    1
10406       98579       22824       B    1
43594       83827       4           S    2
43594       38475       543         S    2

Keeping `StartPoint` allows you to terminate a cycle like `1 → 2 → 3 → 1 ...`. Saving the complete path, as shown [here](https://stackoverflow.com/questions/42119057/sql-recursive-cte-finding-objects-linked-by-property/42139978#42139978), allows terminating any cycle, e.g. `1 → 2 → 3 → 2 ...`. Your solution is much simpler if cycles always return to their starting point. — HABO, Nov 23 '20 at 18:09
This works excellent in combination with @HABOs example :) Thanks a lot — SqlKindaGuy, Nov 25 '20 at 14:04

exclude self-referencing elements recursive cte

1 Answers1