Prevent Recursive Cte Visiting Nodes Multiple Times

Prevent recursive CTE visiting nodes multiple times

The CTE's are recursive.

When your CTE's have multiple initial conditions, that means they also have different recursion stacks, and there is no way to use information from one stack in another stack.

In your example, the recursion stacks will go as follows:

(1) - first IN condition
(1, 2)
(1, 2, 3)
(1, 2, 3, 4)
(1, 2, 3) - no more children
(1, 2) - no more children
(1) - no more children, going to second IN condition

(3) - second condition
(3, 4)
(3) - no more children, returning

As you can see, these recursion stack do not intersect.

You could probably record the visited values in a temporary table, JOIN each value with the temptable and do not follow this value it if it's found, but SQL Server does not support these things.

So you just use SELECT DISTINCT.

How to stop recursion in a CTE?

Since you are iterating here and have the cte's last id that it picked up available in your recursive term you can just filter out results where the last iteration hit 'y'

WITH cte (ID, Data)
AS
(
  -- base case
  SELECT x.ID, x.Data
  FROM MyTable AS x
  WHERE x.PredecessorID IS NULL

  UNION ALL

  -- other cases
  SELECT x.ID, x.Data
  FROM MyTable as x
  INNER JOIN cte
    ON x.PredecessorID = cte.ID
  WHERE cte.id <> 'y'
)
SELECT * FROM cte;

Note that if your x id has many branches, some of which don't lead to 'y' then those branches will keep iterating until they hit their natural end. Only branch leading to y will be prematurely stopped here.

How use recursive CTE content for specific child node?

Seemingly, you should swap columns for the join condition and change the where search condition according to your task condition.

WITH cte_data (c_key, p_key, depth) AS
(
    SELECT c_key, p_key, 0 AS depth
    FROM my_table
    WHERE c_key = 'child'

    UNION ALL

    SELECT c.c_key, c.p_key, o.depth + 1 AS depth
    FROM cte_data o
    INNER JOIN my_table c ON o.p_key = c.c_key
)
SELECT * 
FROM cte_data
ORDER by depth

exclude self-referencing elements recursive cte

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

---

Related Topics