How can I Ascertain the structure for each person from a self referencing table

Question

I have the following tables:

Employees
-------------
ClockNo     int
CostCentre  varchar
Department  int

and

Departments
-------------
DepartmentCode  int
CostCentreCode  varchar
Parent          int

Departments can have other departments as parents meaning there is infinite hierarchy. All departments belong to a cost centre and so will always have a CostCentreCode. If parent = 0 it is a top level department

Employees must have a CostCentre value but may have a Department of 0 meaning they are not in a department

What I want to try and generate is a query that will give the up to four levels of hierarchy. Like this:

EmployeesLevels
-----------------
ClockNo
CostCentre
DeptLevel1
DeptLevel2
DeptLevel3
DeptLevel4

I've managed to get something to display the department structure on it's own, but I can't work out how to link this to the employees without creating duplicate employee rows:

SELECT d1.Description AS lev1, d2.Description as lev2, d3.Description as lev3, d4.Description as lev4
FROM departments AS d1
LEFT JOIN departments AS d2 ON d2.parent = d1.departmentcode
LEFT JOIN departments AS d3 ON d3.parent = d2.departmentcode
LEFT JOIN departments AS d4 ON d4.parent = d3.departmentcode
WHERE d1.parent=0;

---

SQL To create Structure and some sample data:

CREATE TABLE Employees(
ClockNo integer NOT NULL PRIMARY KEY,
CostCentre varchar(20) NOT NULL,
Department integer NOT NULL);

CREATE TABLE Departments(
DepartmentCode integer NOT NULL PRIMARY KEY,
CostCentreCode varchar(20) NOT NULL,
Parent integer NOT NULL
);

CREATE INDEX idx0 ON Employees (ClockNo);
CREATE INDEX idx1 ON Employees (CostCentre, ClockNo);
CREATE INDEX idx2 ON Employees (CostCentre);

CREATE INDEX idx0 ON Departments (DepartmentCode);
CREATE INDEX idx1 ON Departments (CostCentreCode, DepartmentCode);

INSERT INTO Employees VALUES (1, 'AAA', 0);
INSERT INTO Employees VALUES (2, 'AAA', 3);
INSERT INTO Employees VALUES (3, 'BBB', 0);
INSERT INTO Employees VALUES (4, 'BBB', 4);
INSERT INTO Employees VALUES (5, 'CCC', 0); 
INSERT INTO Employees VALUES (6, 'AAA', 1);
INSERT INTO Employees VALUES (7, 'AAA', 5);
INSERT INTO Employees VALUES (8, 'AAA', 15);

INSERT INTO Departments VALUES (1, 'AAA', 0);
INSERT INTO Departments VALUES (2, 'AAA', 1);
INSERT INTO Departments VALUES (3, 'AAA', 1);
INSERT INTO Departments VALUES (4, 'BBB', 0);
INSERT INTO Departments VALUES (5, 'AAA', 3);
INSERT INTO Departments VALUES (12, 'AAA', 5);
INSERT INTO Departments VALUES (15, 'AAA', 12);

This gives the following structure (employee clock numbers in square brackets):

Root
  |
  |---AAA                   [1]
  |    \---1                [6]
  |       |---2     
  |       \---3             [2]
  |          \---5          [7]
  |             \---12
  |                \---15   [8]
  |
  |---BBB                   [3]
  |    \---4                [4]
  |
  \---CCC                   [5]

The query should return the following:

ClockNo CostCentre Level1 Level2 Level3 Level4
1       AAA        
2       AAA        1      3
3       BBB
4       BBB        4
5       CCC
6       AAA        1
7       AAA        1      3       5
8       AAA        1      3       5      12  *

* In the case of Employee 8, they are in level5. Ideally I would like to show all their levels down to level4, but I am happy just to show the CostCentre in this case

Answer 1

When we join the tables we should stop further traversal of the path when we found proper department that belongs to the Employee at previous level.

Also we have exceptional case when Employee.Department=0. In this case we shouldn't join any of departments, because in this case Department is the Root.

We need to choose only those records which contains employee's Department at one of the levels. In case if employee's department level is greater than 4 we should expand all 4 levels of departments and show them as is (even if can't reach the desired department level and didn't find it within expanded ones).

select e.ClockNo, 
       e.CostCentre, 
       d1.DepartmentCode as Level1, 
       d2.DepartmentCode as Level2, 
       d3.DepartmentCode as Level3, 
       d4.DepartmentCode as Level4
from Employees e
left join Departments d1 
          on e.CostCentre=d1.CostCentreCode 
          and d1.Parent=0 
          and ((d1.DepartmentCode = 0 and e.Department = 0) or e.Department <> 0)
left join Departments d2 
          on d2.parent=d1.DepartmentCode 
          and (d1.DepartMentCode != e.Department and e.Department<>0)
left join Departments d3 
          on d3.parent=d2.DepartmentCode 
          and (d2.DepartMentCode != e.Department and e.Department<>0)
left join Departments d4 
          on d4.parent=d3.DepartmentCode 
          and (d3.DepartMentCode != e.Department and e.Department<>0)
where e.Department=d1.DepartmentCode 
      or e.Department=d2.DepartmentCode 
      or e.Department=d3.DepartmentCode 
      or e.Department=d4.DepartmentCode 
      or e.Department=0
      or (
        (d1.DepartmentCode is not null) and
        (d2.DepartmentCode is not null) and
        (d3.DepartmentCode is not null) and
        (d4.DepartmentCode is not null)
      )
order by e.ClockNo;

Answer 2

SELECT  [ClockNo]
    ,   [CostCentre]    
    ,   CASE
            WHEN Department <> 0 THEN dept.[Level1]         
        END AS [Level1]
    ,   CASE
            WHEN Department <> 0 THEN dept.[Level2]         
        END AS [Level2]
    ,   CASE
            WHEN Department <> 0 THEN dept.[Level3]         
        END AS [Level3]
    ,   CASE
            WHEN Department <> 0 THEN dept.[Level4]         
        END AS [Level4]

FROM    [Employees] emp
LEFT JOIN
(
SELECT  
        CASE 
            WHEN d4.[DepartmentCode] IS NOT NULL THEN d4.[DepartmentCode]
            WHEN d3.[DepartmentCode] IS NOT NULL THEN d3.[DepartmentCode]
            WHEN d2.[DepartmentCode] IS NOT NULL THEN d2.[DepartmentCode]
            ELSE d1.[DepartmentCode]
        END     AS  [Level1]
    ,   CASE 
            WHEN d4.[DepartmentCode] IS NOT NULL THEN d3.[DepartmentCode]
            WHEN d3.[DepartmentCode] IS NOT NULL THEN d2.[DepartmentCode]
            WHEN d2.[DepartmentCode] IS NOT NULL THEN d1.[DepartmentCode]
            ELSE NULL
        END     AS  [Level2]
    ,   CASE 
            WHEN d4.[DepartmentCode] IS NOT NULL THEN d2.[DepartmentCode]
            WHEN d3.[DepartmentCode] IS NOT NULL THEN d1.[DepartmentCode]           
            ELSE NULL
        END     AS  [Level3]
    ,   CASE 
            WHEN d4.[DepartmentCode] IS NOT NULL THEN d1.[DepartmentCode]           
            ELSE NULL
        END     AS  [Level4]
    ,   d1.[DepartmentCode] AS  [DepartmentCode]    
    ,   d1.[CostCentreCode] AS  [CostCenter]
FROM    [Departments] d1
LEFT JOIN
        [Departments] d2
ON      d1.[Parent] = d2.[DepartmentCode]
LEFT JOIN
        [Departments] d3
ON      d2.[Parent] = d3.[DepartmentCode]
LEFT JOIN
        [Departments] d4
ON      d3.[Parent] = d4.[DepartmentCode]
) AS dept
ON  emp.[Department] = dept.[DepartmentCode]
ORDER BY emp.[ClockNo]

Answer 3

The main challenge here is that the employee's department might need to be displayed in column Level1, Level2, Level3, or Level4, depending on how many upper levels there are for that department in the hierarchy.

I would suggest to first query the number of department levels there are for each employee in an inner query, and then to use that information to put the department codes in the right column:

SELECT    ClockNo, CostCentre,
          CASE LevelCount
             WHEN 1 THEN Dep1
             WHEN 2 THEN Dep2
             WHEN 3 THEN Dep3
             ELSE        Dep4
          END Level1,
          CASE LevelCount
             WHEN 2 THEN Dep1
             WHEN 3 THEN Dep2
             WHEN 4 THEN Dep3
          END Level2,
          CASE LevelCount
             WHEN 3 THEN Dep1
             WHEN 4 THEN Dep2
          END Level3,
          CASE LevelCount
             WHEN 4 THEN Dep1
          END Level4
FROM      (SELECT   e.ClockNo, e.CostCentre, 
                    CASE WHEN d2.DepartmentCode IS NULL THEN 1
                      ELSE CASE WHEN d3.DepartmentCode IS NULL THEN 2
                        ELSE CASE WHEN d4.DepartmentCode IS NULL THEN 3
                           ELSE 4
                        END
                      END
                    END AS LevelCount,
                    d1.DepartmentCode Dep1, d2.DepartmentCode Dep2,
                    d3.DepartmentCode Dep3, d4.DepartmentCode Dep4
          FROM      Employees e
          LEFT JOIN departments AS d1 ON d1.DepartmentCode = e.Department
          LEFT JOIN departments AS d2 ON d2.DepartmentCode = d1.Parent
          LEFT JOIN departments AS d3 ON d3.DepartmentCode = d2.Parent
          LEFT JOIN departments AS d4 ON d4.DepartmentCode = d3.Parent) AS Base
ORDER BY  ClockNo

SQL Fiddle

Alternatively, you could do a plain UNION ALL of the 5 possible scenarios in terms of existing levels (chains of 0 to 4 departments):

SELECT     ClockNo, CostCentre,       d4.DepartmentCode Level1,
           d3.DepartmentCode Level2,  d2.DepartmentCode Level3,
           d1.DepartmentCode Level4
FROM       Employees e
INNER JOIN departments AS d1 ON d1.DepartmentCode = e.Department
INNER JOIN departments AS d2 ON d2.DepartmentCode = d1.Parent
INNER JOIN departments AS d3 ON d3.DepartmentCode = d2.Parent
INNER JOIN departments AS d4 ON d4.DepartmentCode = d3.Parent
UNION ALL
SELECT     ClockNo, CostCentre, d3.DepartmentCode,
           d2.DepartmentCode,   d1.DepartmentCode, NULL
FROM       Employees e
INNER JOIN departments AS d1 ON d1.DepartmentCode = e.Department
INNER JOIN departments AS d2 ON d2.DepartmentCode = d1.Parent
INNER JOIN departments AS d3 ON d3.DepartmentCode = d2.Parent
WHERE      d3.Parent = 0
UNION ALL
SELECT     ClockNo, CostCentre, d2.DepartmentCode,
           d1.DepartmentCode,   NULL, NULL
FROM       Employees e
INNER JOIN departments AS d1 ON d1.DepartmentCode = e.Department
INNER JOIN departments AS d2 ON d2.DepartmentCode = d1.Parent
WHERE      d2.Parent = 0
UNION ALL
SELECT     ClockNo, CostCentre, d1.DepartmentCode Level1,
           NULL, NULL, NULL
FROM       Employees e
INNER JOIN departments AS d1 ON d1.DepartmentCode = e.Department
WHERE      d1.Parent = 0
UNION ALL
SELECT     ClockNo, CostCentre, NULL, NULL, NULL, NULL
FROM       Employees e
WHERE      e.Department = 0
ORDER BY   ClockNo

SQL Fiddle