Hierarchial SQL Query

Question

Hi,

I have a self-referencing table (Customers) and a table that will link to one record in that table (Companies) i.e.

Customers      Companies
*********      *********
ID             ID
ManagerID  --> DirectorID

ManagerID refers to another record in the Customer table.

I need to perform a query where by given a specific customer ID, it will find the Company that customer belongs to. If I was to do this in C# it would look something like (this is sample code, not functional):

public static Company FindCompany(Customer customer)
{
     while (customer.ManagerID != null)
     {
          customer = customer.GetManager();
     }
     return Company.FindByDirector(customer.ID);
}

So there are 2 steps:

1) Traverse up the Customer table (via ManagerID) until we find a Customer with no ManagerID. (The Director)

2) Find the Company relating to that Customer.

Can anyone help me out?

Thanks.

Answer 1

Something like this, untested though.

Based on CTEs Recursive Queries Using Common Table Expressions

WITH cTE AS
(
    SELECT --Get manager of given customer
        ManagerID
    FROM
        Customers
    WHERE
        ID = @MyCustomerID
    UNION ALL
    SELECT --Get manager of the manager etc
        Customers.ManagerID
    FROM
        cTE
        JOIN
        Customers ON cTE.ManagerID = Customers.ID
)
SELECT
    *
FROM
    cTE
    JOIN
    Company ON cTE.ManagerID = Company.DirectorID