Getting rows and related rows

Question

I have a table like this...

RowID  FromCustomer  ToCustomer  
    1             A           B
    2             A           C
    3             C           D
    4             E           F
    5             E           G

Now I would like to search for Customer C (that is RowID 2 and 3), but my challange is that I also wants the rows that are related to Customer C ... and that is RowID 1 because Customer A is related to C via RowID 2.

So searching for Customer C must return:

RowID  FromCustomer  ToCustomer  
    1             A           B  
    2             A           C  
    3             C           D  

I don't know if I can use recursive - but this is my code so far, that does not work:

DECLARE @FindCustomer NCHAR(1) = 'C';

WITH
        RecursiveTestCTE(RowID, FromCustomer, ToCustomer)
        AS
        (
            SELECT RowID, FromCustomer, ToCustomer
            FROM [dbo].[RecursiveTest]
            WHERE FromCustomer = @FindCustomer OR ToCustomer = @FindCustomer 

            UNION ALL

            SELECT u.RowID, u.FromCustomer, u.ToCustomer
            FROM [dbo].[RecursiveTest] u
            JOIN RecursiveTestCTE
            ON RecursiveTestCTE.FromCustomer = RecursiveTestCTE.FromCustomer
        )
SELECT r.RowID, r.FromCustomer, r.ToCustomer
FROM   RecursiveTestCTE r

I'm kinda stuck, so I ask for your help.

UPDATE:

Here's what I mean by related...

• In row 3 customer C owns Customer D.
• In row 2 customer A owns Customer C.
• In row 1 customer A owns Customer B.

That makes Customer C, D, A and B in the same organisation - and that is the result I want.

Customer E, F and G is not in the same organisation.

UPDATE:

Here's how I would do it in VB.Net...

Private Sub Run()
    Dim searchFor As String = "C"
    Dim customers As New List(Of String)
    IterateCustomers(customers, searchFor)
End Sub

Private Sub IterateCustomers(ByRef customers As List(Of String), searchFor As String)
    If Not customers.Contains(searchFor) Then customers.Add(searchFor)

    For Each row In (From x In MyDatabaseTable Where x.FromCustomer = searchFor OrElse x.ToCustomer = searchFor).ToList
        IterateCustomers(customers, row.FromCustomer)
        IterateCustomers(customers, row.ToCustomer)
    Next
End Sub

Answer 1

Bit of a blind guess, but I think this might this might be what you're after? I feel like we're missing some rows in hierarchy here though:

USE Sandbox;
GO

CREATE TABLE dbo.YourTable (RowID int,
                            FromCustomer char(1),
                            ToCustomer char(1));
INSERT INTO dbo.YourTable (RowID,
                           FromCustomer,
                           ToCustomer)
VALUES (1, 'A', 'B'),
       (2, 'A', 'C'),
       (3, 'C', 'D'),
       (4, 'E', 'F'),
       (5, 'E', 'G');
GO

DECLARE @Customer char(1) = 'C';

WITH rCTE1 AS(
    SELECT YT.RowID,
           YT.FromCustomer,
           YT.ToCustomer
    FROM dbo.YourTable YT
    WHERE YT.FromCustomer = @Customer
    UNION ALL
    SELECT YT.RowID,
           YT.FromCustomer,
           YT.ToCustomer
    FROM dbo.YourTable YT
         JOIN rCTE1 r1 ON YT.ToCustomer = r1.FromCustomer),
rCTE2 AS(
    SELECT YT.RowID,
           YT.FromCustomer,
           YT.ToCustomer
    FROM rCTE1 r1
         JOIN dbo.YourTable YT ON r1.FromCustomer = YT.FromCustomer
    AND NOT EXISTS (SELECT e.RowID
                    FROM rCTE1 e
                    WHERE e.RowID = YT.RowID)
    UNION ALL
    SELECT YT.RowID,
           YT.FromCustomer,
           YT.ToCustomer
    FROM dbo.YourTable YT
         JOIN rCTE2 r2 ON YT.FromCustomer = r2.ToCustomer)
SELECT r1.RowID,
       r1.FromCustomer,
       r1.ToCustomer
FROM rCTE1 r1
UNION ALL
SELECT r2.RowID,
       r2.FromCustomer,
       r2.ToCustomer
FROM rCTE2 r2
ORDER BY RowID ASC;
GO


DROP TABLE dbo.YourTable;