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
SunnyMagadan's query is good. But depending on number of employees in a department you may wish to try the following one which leaves DB optimizer an opportunity to traverse department hierarchy only once for a department instead of repeating it for every employee in a department.
SELECT e.ClockNo, e.CostCentre, Level1, Level2, Level3, Level4
FROM Employees e
LEFT JOIN
(SELECT
d1.departmentcode
, d1.CostCentreCode
, coalesce (d4.departmentcode, d3.departmentcode
, d2.departmentcode, d1.departmentcode) 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 end as Level2
, case when d4.departmentcode is not null then d2.departmentcode
when d3.departmentcode is not null then d1.departmentcode end as Level3
, case when d4.departmentcode is not null then d1.departmentcode end as Level4
FROM departments AS d1
LEFT JOIN departments AS d2 ON d1.parent = d2.departmentcode
LEFT JOIN departments AS d3 ON d2.parent = d3.departmentcode
LEFT JOIN departments AS d4 ON d3.parent = d4.departmentcode) d
ON d.DepartmentCode = e.Department AND d.CostCentreCode = e.CostCentre
;
EDIT Regarding level 5+ departments.
Any fixed step query can not get top 4 levels for them. So change above query just to mark them some way, -1 for example.
, case when d4.Parent > 0 then NULL else
coalesce (d4.departmentcode, d3.departmentcode
, d2.departmentcode, d1.departmentcode) end AS Level1
and so on.