How can I determine the structure for each person from the self-reference table

Question

I have the following tables:

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

and

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

Departments may have other departments, as parents mean that there is an infinite hierarchy. All departments are cost CostCentreCode , so there will always be a CostCentreCode . If parent = 0 this is the top-level department

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

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

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

I managed to get something to display the structure of the department on it, but I cannot figure out how to associate this with employees without creating duplicate rows of employees:

 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 a 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 hours numbers in square brackets):

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

The request 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 at level 5. Ideally, I would like to show all levels up to level 4, but I'm happy to just show CostCentre in this case

Answer 1

SunnyMagadan request is good. But, depending on the number of employees in the department, you can try the following, which gives the database optimizer the opportunity to go through the hierarchy of departments only once for the department, and not repeat it for each employee in the 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 departments 5+.

Any request with a fixed step cannot get the top 4 levels for them. Therefore, modify the query above to mark them somehow, for example -1.

 , case when d4.Parent > 0 then NULL else coalesce (d4.departmentcode, d3.departmentcode , d2.departmentcode, d1.departmentcode) end AS Level1 

etc.

Answer 2

When we join the tables, we must stop further going around the path when we find the right department that belongs to the Employer at the previous level.

We also have an exceptional case when Employee.Department = 0. In this case, we should not join any of the departments, because in this case the department is the root.

We need to select only those records that contain the department of employees at one of the levels. If the level of the department of employees exceeds 4, we must expand all 4 levels of departments and show them as is (even if they cannot reach the desired level of the department and cannot find it in the 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 3

The main task here is that the department of employees can be displayed in the columns Level1, Level2, Level3 or Level4, depending on the number of upper levels for this department in the hierarchy.

I would suggest first asking for the number of department levels for each employee in an internal query, and then using this information to put 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 can make a simple UNION ALL of 5 possible scenarios in terms of existing levels (chains from 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

Answer 4

 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 5

Try this request. Not sure how he will demonstrate his performance in big data with this COALESCE .

The idea is to build a hierarchy view leading to each Department

 lev1 lev2 lev3 lev4 1 NULL NULL NULL 1 2 NULL NULL 1 3 NULL NULL 1 3 5 NULL 4 NULL NULL NULL 

and then use the rightmost department to join it with Employees . Here's the full request:

  SELECT ClockNo, CostCentre, lev1, lev2, lev3, lev4 FROM Employees LEFT JOIN ( SELECT d1.DepartmentCode AS lev1, NULL as lev2, NULL as lev3, NULL as lev4 FROM departments AS d1 WHERE d1.parent=0 UNION ALL SELECT d1.DepartmentCode AS lev1, d2.DepartmentCode as lev2, NULL as lev3, NULL as lev4 FROM departments AS d1 JOIN departments AS d2 ON d2.parent = d1.departmentcode WHERE d1.parent=0 UNION ALL SELECT d1.DepartmentCode AS lev1, d2.DepartmentCode as lev2, d3.DepartmentCode as lev3, NULL as lev4 FROM departments AS d1 JOIN departments AS d2 ON d2.parent = d1.departmentcode JOIN departments AS d3 ON d3.parent = d2.departmentcode WHERE d1.parent=0 UNION ALL SELECT d1.DepartmentCode AS lev1, d2.DepartmentCode as lev2, d3.DepartmentCode as lev3, d4.DepartmentCode as lev4 FROM departments AS d1 JOIN departments AS d2 ON d2.parent = d1.departmentcode JOIN departments AS d3 ON d3.parent = d2.departmentcode JOIN departments AS d4 ON d4.parent = d3.departmentcode WHERE d1.parent=0 ) Department ON COALESCE(Department.lev4, Department.lev3, Department.lev2, Department.lev1) = Employees.Department ORDER BY ClockNo 

Answer 6

I would suggest that you split the request for an employee and getting his / her department hierarchy.

To get the department hierarchy, I suggest you use a recursive CTE something like this:

 with DepartmentList (DepartmentCode, CostCentreCode, Parent) AS ( SELECT parentDepartment.DepartmentCode, parentDepartment.CostCentreCode, parentDepartment.Parent FROM Departments parentDepartment WHERE DepartmentCode = @departmentCode UNION ALL SELECT childDepartment.DepartmentCode childDepartment.CostCentreCode, childDepartment.Parent, FROM Departments childDepartment JOIN DepartmentList ON childDepartment.Parent = DepartmentList.DepartmentCode ) SELECT * FROM DepartmentList 

This is not a direct answer to your question, but it will give you a choice and an idea. Hope this helps.

Answer 7

So, I took two steps to do this:

• I had to generate levels for recursions recursively
• Create all possible parent nodes so that I can display them in a collapsible view

This recursive query builds DepartmentLevels:

 ;WITH CTE (DepartmentCode, CostCentreCode, Parent, DepartmentLevel) AS ( SELECT D.DepartmentCode, D.CostCentreCode, D.Parent, 1 FROM dbo.Departments AS D WHERE D.Parent = 0 UNION ALL SELECT D.DepartmentCode, D.CostCentreCode, D.Parent, C.DepartmentLevel + 1 FROM dbo.Departments AS D INNER JOIN CTE AS C ON C.DepartmentCode = D.Parent AND C.CostCentreCode = D.CostCentreCode ) SELECT * INTO #DepartmentLevels FROM CTE; 

What is the conclusion:

 ╔════════════════╦════════════════╦════════╦═════════════════╗ ║ DepartmentCode ║ CostCentreCode ║ Parent ║ DepartmentLevel ║ ╠════════════════╬════════════════╬════════╬═════════════════╣ ║ 1 ║ AAA ║ 0 ║ 1 ║ ║ 4 ║ BBB ║ 0 ║ 1 ║ ║ 2 ║ AAA ║ 1 ║ 2 ║ ║ 3 ║ AAA ║ 1 ║ 2 ║ ║ 5 ║ AAA ║ 3 ║ 3 ║ ╚════════════════╩════════════════╩════════╩═════════════════╝ 

Now this query will generate all possible parent nodes for each node (view of the mapping table):

 ;WITH CTE (DepartmentCode, CostCentreCode, Parent, DepartmentLevelCode) AS ( SELECT D.DepartmentCode, D.CostCentreCode, D.Parent, D.DepartmentCode FROM dbo.Departments AS D UNION ALL SELECT D.DepartmentCode, D.CostCentreCode, D.Parent, C.DepartmentLevelCode FROM dbo.Departments AS D INNER JOIN CTE AS C ON C.Parent = D.DepartmentCode ) SELECT * FROM CTE; 

What gives us this result:

 ╔════════════════╦════════════════╦════════╦═════════════════════╗ ║ DepartmentCode ║ CostCentreCode ║ Parent ║ DepartmentLevelCode ║ ╠════════════════╬════════════════╬════════╬═════════════════════╣ ║ 1 ║ AAA ║ 0 ║ 1 ║ ║ 2 ║ AAA ║ 1 ║ 2 ║ ║ 3 ║ AAA ║ 1 ║ 3 ║ ║ 4 ║ BBB ║ 0 ║ 4 ║ ║ 5 ║ AAA ║ 3 ║ 5 ║ ║ 3 ║ AAA ║ 1 ║ 5 ║ ║ 1 ║ AAA ║ 0 ║ 5 ║ ║ 1 ║ AAA ║ 0 ║ 3 ║ ║ 1 ║ AAA ║ 0 ║ 2 ║ ╚════════════════╩════════════════╩════════╩═════════════════════╝ 

Now we can combine these three buddies together with the Employees table and get the desired result:

 ;WITH CTE (DepartmentCode, CostCentreCode, Parent, DepartmentLevelCode) AS ( SELECT D.DepartmentCode, D.CostCentreCode, D.Parent, D.DepartmentCode FROM dbo.Departments AS D UNION ALL SELECT D.DepartmentCode, D.CostCentreCode, D.Parent, C.DepartmentLevelCode FROM dbo.Departments AS D INNER JOIN CTE AS C ON C.Parent = D.DepartmentCode ) SELECT E.ClockNo , E.CostCentre , C.Level1 , C.Level2 , C.Level3 , C.Level4 FROM dbo.Employees AS E OUTER APPLY ( SELECT MAX(CASE WHEN DL.DepartmentLevel = 1 THEN C.DepartmentCode END) , MAX(CASE WHEN DL.DepartmentLevel = 2 THEN C.DepartmentCode END) , MAX(CASE WHEN DL.DepartmentLevel = 3 THEN C.DepartmentCode END) , MAX(CASE WHEN DL.DepartmentLevel = 4 THEN C.DepartmentCode END) FROM CTE AS C INNER JOIN #DepartmentLevels AS DL ON DL.DepartmentCode = C.DepartmentCode WHERE C.DepartmentLevelCode = E.Department ) AS C(Level1, Level2, Level3, Level4); 

This will give 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 ║ ║ ╚═════════╩════════════╩════════╩════════╩════════╩════════╝ 

This query will find a DepartmentLevelCode matching based on DepartmentCode and will be based on DepartmentLevel . Hope this is correct.