CTE to build a list of departments and managers (hierarchical)

Question

I need to generate a list of users that are managers, or managers of managers, for company departments.

I have two tables; one details the departments and one contains the manager hierarchy (simplified):

CREATE TABLE [dbo].[Manager](
[ManagerId] [int],
[ParentManagerId] [int])

CREATE TABLE [dbo].[Department](
[DepartmentId] [int],
[ManagerId] [int])

Basically, I'm trying to build a CTE that will give me a list of DepartmentIds, together with all ManagerIds that are in the manager hierarchy for that department.

So... Say Manager 1 is the Manager for Department 1, and Manager 2 is Manager 1's Manager, and Manager 3 is Manager 2's Manager, I'd like to see:

DepartmentId, ManagerId
1, 1
1, 2
1, 3

Basically, managers are able to deal with all of their sub-manager's departments.

Building the CTE to return the Manager hierarchy was fairly simple, but I'm struggling to inject the Departments in there:

WITH DepartmentManagers
AS
(
    SELECT      ManagerId,
                ParentManagerId,
                0 AS Depth
    From        Manager

    UNION ALL

    SELECT      Manager.ManagerId,
                Manager.ParentManagerId,
                DepartmentManagers.Depth + 1 AS Depth
    FROM        Manager
    INNER JOIN  DepartmentManagers
                ON DepartmentManagers.ManagerId = Manager.ParentManagerId
)

I need a list of all Departments together with all related Managers.

Can anyone help?

Answer 1

Change your anchor query:

WITH    DepartmentManagers
        AS
        (
        SELECT  d.DepartmentID,
                d.ManagerId,
                m.ParentManagerId
                0 AS Depth
        FROM    Department d
        JOIN    Manager m
        ON      m.ManagerID = d.managerID
        WHERE   DepartmentID = 1
        UNION ALL
        SELECT  d.DepartementID,
                m.ManagerId,
                m.ParentManagerId,
                d.Depth + 1
        FROM    DepartmentManagers d
        JOIN    Manager m
        ON      m.ManagerId = d.ParentManagerID
        )