Matt Wilko Matt Wilko - 7 months ago 8
SQL Question

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

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 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);


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

Root
|
|---AAA [1]
| \---1 [6]
| |---2
| \---3 [2]
| \---5 [7]
|
|---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

Answer

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.

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
order by e.ClockNo;
Comments