CODEWITHSUNDEEP
sqlsqliterecursioncommon-table-expression
Bryn M.
Bryn M.

Reputation: 45

SQL recursive CTE with two tables

Trying to wrap my head around recursive CTEs but having some trouble. I've got two tables, one for employees and one for departments. I'm trying to write a recursive CTE that starts with an anchor department and goes through all sub-departments, returning a set of employees that have foreign keys to the anchor department or any sub-departments.

Here is a simplified version of the employee table

employeeID     name               departmentID
1              Brad Fisher        1
2              Alex McCabe        2
3              Amy Calvin         2
4              Daniel Struthers   4

And the department table. The parentID simply referring to other departments in the same table.

departmentID    name                      parentID
1               Company Operations        0
2               Guest Services            1
3               Staff Services            1
4               IT Support                3

Below is the CTE I have put together. As long as there is at least one employee in each level of the department hierarchy, it will return a full list of all employees starting at the top. But the issue is that if there is a department in the hierarchy with no employees, it breaks the recursion there. 

WITH EmployeeDepartmentHierarchy(employeeID, name, departmentID)
AS (
   SELECT e.employeeID,
          e.name,
          e.departmentID
   FROM Departments AS d
   INNER JOIN Employees AS e ON d.departmentID = e.departmentID
   WHERE d.name = "Company Operations"
   UNION ALL
   SELECT e.employeeID,
          e.name,
          d.deparmentID
   FROM Departments AS d
   INNER JOIN EmployeeDepartmentHierarchy AS edh ON edh.departmentID = d.parentID
   LEFT JOIN Employees AS e ON d.departmentID = e.departmentID
   )
   SELECT * FROM EmployeeDepartmentHierarchy

I'm trying to fix it so that it will go down the entire hierarchy of departments and only terminate if there are no sub-departments found, rather than terminating when it reaches a department with no employees.

Upvotes: 1

Views: 3142

Answers (1)

sticky bit
sticky bit

Reputation: 37472

As wildplasser already commented, get the departments without employees in a CTE and then left join them in the outer query.

WITH dh
     (departmentid)
AS
(
SELECT d.departmentid
       FROM departments d
       WHERE d.name = 'Company Operations'
UNION ALL
SELECT d.departmentid
       FROM departments d
            INNER JOIN dh
                       ON dh.departmentid = d.parentid
)
SELECT e.employeeid,
       e.name,
       dh.departmentid
       FROM dh
            LEFT JOIN employees e
                      ON e.departmentid = dh.departmentid;

db<>fiddle

Upvotes: 3

Related Questions