CODEWITHSUNDEEP
sql-serverfunctionrecursionself-join
gluecks
gluecks

Reputation: 1705

SQL Server : how can I achieve this...possibly with a recursive function?

I have the following example table:

customer data

Each customer_number may have one or several debtor_id, and vice versa each debtor_id may have one or more customer_numbers. Now I basically need a way to retrieve all customer_numbers or debtor_ids from any given customer_number or debtor_id

A more specific example:

Say you want to know all customer_numbers that are connected to the debtor_id 24.

So in the end when inputting the debtor_id 24, you'd expect to retrieve the customer_number customer10, customer11 and customer12 as a result.

I know it'll probably require several self-joins, but it needs to be flexible and work dynamically for any given "depth". My first thought was that this heavily smells like recursion. Basically something like "keep self-joining until there are no new customer_numbers or debtor_id returned"

I don't have much experience with recursion, same goes for writing SQL Functions :-(

Does anybody have some tips or helpful advice on this?

Example code to generate the same table:

DECLARE @customers TABLE
                   (
                       customer_number VARCHAR(16),
                       debtor_id INT
                   );

INSERT INTO @customers SELECT 'customer10', 20;
INSERT INTO @customers SELECT 'customer10', 22;
INSERT INTO @customers SELECT 'customer11', 22;
INSERT INTO @customers SELECT 'customer11', 23;
INSERT INTO @customers SELECT 'customer12', 23;
INSERT INTO @customers SELECT 'customer12', 24;
INSERT INTO @customers SELECT 'customer15', 30;
INSERT INTO @customers SELECT 'customer16', 31;
INSERT INTO @customers SELECT 'customer16', 32;
INSERT INTO @customers SELECT 'customer21', 31;
INSERT INTO @customers SELECT 'customer21', 35;

SELECT * 
FROM @customers;

Upvotes: 0

Views: 253

Answers (1)

jon antoine
jon antoine

Reputation: 396

You can accomplish this with recursive CTE's like the ones below

-- Find all customers for debtor
WITH CTE AS (
    SELECT [debtor_id] AS [Root],
        [debtor_id],
        [customer_number],
        CAST('' AS VARCHAR(2000)) AS [Path]
    FROM @customers AS [b]
    UNION ALL
    SELECT [b].[Root],
        [c].[debtor_id],
        [d].[customer_number],
        CAST([b].[Path] + CAST([c].[debtor_id] AS VARCHAR(10)) + '||' AS VARCHAR(2000)) AS [Path]
    FROM [CTE] AS [b]
        INNER JOIN @customers AS [c] ON [b].[customer_number] = [c].[customer_number]
        INNER JOIN @customers AS [d] ON [d].[debtor_id] = [c].[debtor_id]
    WHERE [c].[debtor_id] != [b].[Root]
        AND [Path] NOT LIKE '%' + CAST([c].[debtor_id] AS VARCHAR(10)) + '||%'
)
SELECT DISTINCT
    [customer_number]
FROM [CTE] AS [c]
WHERE [Root] = 24;

-- Find all debtors for customer
WITH CTE AS (
    SELECT [customer_number] AS [Root],
        [debtor_id],
        [customer_number],
        CAST('' AS VARCHAR(2000)) AS [Path]
    FROM @customers AS [b]
    UNION ALL
    SELECT [b].[Root],
        [d].[debtor_id],
        [c].[customer_number],
        CAST([b].[Path] + [c].[customer_number] + '||' AS VARCHAR(2000)) AS [Path]
    FROM [CTE] AS [b]
        INNER JOIN @customers AS [c] ON [b].[debtor_id] = [c].[debtor_id]
        INNER JOIN @customers AS [d] ON [d].[customer_number] = [c].[customer_number]
    WHERE [c].[customer_number] != [b].[Root]
        AND [Path] NOT LIKE '%' + [c].[customer_number] + '||%'
)
SELECT DISTINCT [debtor_id]
FROM [CTE] AS [c]
WHERE [Root] = 'customer10';

Upvotes: 2

Related Questions