Recurcive cte to identify circular reference in data

Question

I am trying to identify recursive/circular reference in my data for which I need recursive cte.

For example I have table that contains Product_ID and Inner_Product_ID. I want results when Product_ID A is inner to Product_ID B, which is inner to Product_ID C, which is inner to Product_ID A.

Sample data

PRODUCT_ID    INNER_PRODUCT_ID
   12              36
   24              12
   36              24
   1               2
   3               4

Expected output

PRODUCT_ID    INNER_PRODUCT_ID
   12              36
   24              12
   36              24

I have tried basic query with cte but not sure how to implement recursive cte for this problem:

;WITH RNCTE
AS (   SELECT *,
           ROW_NUMBER() OVER (PARTITION BY pr1.PRODUCT_ID
                              ORDER BY pr1.PRODUCT_ID
                             ) rn
       FROM
           TableName pr1),
      cte
AS (   SELECT *
       FROM RNCTE
       WHERE RNCTE.rn = 1
       UNION ALL
       SELECT *
       FROM cte   c
           JOIN RNCTE r
                   ON r.PRODUCT_ID = c.PRODUCT_ID
                      AND r.rn = c.rn + 1)
SELECT *
FROM  cte;

Are you only prepared to use a recursive CTE? Would you accept program type logic and temp tables? I'm not saying there is no CTE to cover all cases. The trouble with CTE is it seems to limit the operations that can be applied to the developing set of data — Cato, Nov 29 '18 at 16:21
@Cato I will be creating a view out of this query, so I am trying to keep the solution as simple as possible and I think recursive cte will keep the logic straightforward. — Aura, Nov 29 '18 at 16:26

score 1 · Answer 1 · answered Nov 29 '18 at 16:46

try this - it walks through the linked records, and finds if the 'walk' eventually terminates, or not. If it lasts for more than the number of records in the table, then it must be a loop. 'Efficient' I am not sure of that!

;WITH UCNT AS (SELECT count(0) c from products),
        RNCTE 
    AS (SELECT 1 as Levle, Product_ID, INNER_PRODUCT_ID FROM Products
        UNION ALL
        SELECT levle + 1, P.Product_ID, P.INNER_PRODUCT_ID
                     FROM RNCTE R 
                        JOIN Products P 
                            ON P.PRODUCT_ID = R.INNER_PRODUCT_ID
        WHERE levle <= (SELECT c + 2 FROM UCNT))
        --when the recursion count levle exceeds the count of records in the table, 
        --we must have recursion, because 
        --termination has to otherwise occur.  The most extreme case is 
        --that all records are linked, with termination
        --after this, we have to be in a 'loop' 
        SELECT TOP 1 with ties * FROM RNCTE order by levle desc
        option (maxrecursion 0)

Thanks for the code, I will test it against my data. – Aura Nov 29 '18 at 16:51 — Aura, Nov 29 '18 at 16:51

score 0 · Answer 2 · answered Nov 29 '18 at 16:15

0

I think you don't need to use CTE or RECUSRIVE CTE :

SELECT pr1.*
FROM TableName pr1
WHERE EXISTS (SELECT 1 FROM TableName pr2 WHERE pr2.INNER_PRODUCT_ID = pr1.PRODUCT_ID);

answered Nov 29 '18 at 16:15

Yogesh Sharma

49,870
5
26
52

1

There is no instance that matches condition `pr2.INNER_PRODUCT_ID = pr1.PRODUCT_ID`, however there is a relation between `pr1.PRODUCT_ID` at **n** level and `pr2.INNER_PRODUCT_ID` at **n+1** level. – Aura Nov 29 '18 at 16:18

Recurcive cte to identify circular reference in data

2 Answers2