Recursive CTE in SQL - How to avoid repeated results?

Question

In my SQL data model, I have a relationship table that links customers with customer accounts. An account can be held by multiple customers, and a customer can hold multiple accounts.

I want to find the relationships between customers, while also displaying the accounts. To this I have created a CTE that given a customernumber, recursively fetches that customer's relationship and a relationship's relationship.

For example, assuming I have the following dataset:

Customer 1, Account 1
Customer 2, Account 1
Customer 2, Account 2
Customer 3, Account 2
Customer 4, Account 3

Running the CTE for customer number 1 i want to fetch customers 1, 2, 3 and accounts 1, 2. However, since this is recursive, i'm also fetching every repeated relation (1 -> 2 -> 1) up to a preset maximum depth. Is there any way i can "flag" an existing relationship so it won't get selected repeatedly?

Here is my CTE, based off of Microsoft's CTE example:

WITH EntityRelations(CUSTOMERNUMBER, ACCOUNTID, RELATEDWITH, Level)
AS
(
    SELECT 
        C.CUSTOMERNUMBER, 
        A.ACCOUNTNUMBER, 
        CREL.CUSTOMER related, 
        0 as level
    FROM CUSTOMER_ACCOUNT CA
    INNER JOIN CUSTOMER C
        ON C.ID = CA.CUSTOMERID
    INNER JOIN ACCOUNT A
        ON A.ID = CA.ACCOUNTID
    --Get direct relationships
    LEFT JOIN CUSTOMER_ACCOUNT CREL
        ON CREL.ACCOUNTID = CA.ACCOUNTID
        AND CREL.CUSTOMERID <> CA.CUSTOMERID
    WHERE BCE.CUSTOMER = 1

    UNION ALL
    --Recursion
    SELECT 
        C.CUSTOMERNUMBER, 
        A.ID, 
        CREL.CUSTOMER related, 
        Level+1
    FROM CUSTOMER_ACCOUNT CA
    INNER JOIN CUSTOMER C
        ON C.ID = CA.CUSTOMERID
    INNER JOIN ACCOUNT A
        ON A.ID = CA.ACCOUNTID
    --Get direct relationships
    LEFT JOIN CUSTOMER_ACCOUNT CREL
        ON CREL.ACCOUNTID = CA.ACCOUNTID
        AND CREL.CUSTOMERID <> CA.CUSTOMERID
    INNER JOIN EntityRelations ER
        ON ER.RELATEDWITH = CA.CUSTOMERID
    WHERE Level < 3 --Maximum
)

SELECT * FROM EntityRelations

You can find a good way to do this in [this answer](https://stackoverflow.com/a/22453893/243373) and [this answer](https://stackoverflow.com/a/11042012/243373) — TT., Jun 09 '17 at 13:54
Possible duplicate of [TSQL CTE: How to avoid circular traversal?](https://stackoverflow.com/questions/11041797/tsql-cte-how-to-avoid-circular-traversal) — TT., Jun 09 '17 at 13:55

Juan Carlos Oropeza · Answer 1 · 2017-06-09T13:56:40.207

In your SELECT keep a csv list of the customers, then check if new Customer is on the list or not.

A basic pseudo code with the idea

 SELECT .... , ',' as lstCustomer   -- empty list

 UNION ALL

 SELECT .... ,  customerID || ',' as lstCustomer -- add new customer

 WHERE ',' || lstCustomer || ',' 
       NOT LIKE '%,' || customerID  || ',%'  
                         -- Check if list already have this customer

EDIT: I was assuming your want limit for the number of appear for CUSTOMER, if you want limit for Customer, Account you do something like this

 UNION ALL

 SELECT .... ,  '{' || customerID || '-' || accountID || '},' as lstCustomer 
                -- add new customer

 WHERE ',' || lstCustomer || ',' 
       NOT LIKE '%,{' || customerID  || '-' || accountID || '},%'  
                         -- Check if list already have this customer

Recursive CTE in SQL - How to avoid repeated results?

1 Answers1