带有两个表的 SQL 递归 CTE

Question

试图围绕递归 CTE 进行思考，但遇到了一些麻烦。我有两张 table ，一张给员工，一张给部门。我正在尝试编写一个递归 CTE，它从一个锚定部门开始并遍历所有子部门，将一组具有外键的员工返回给锚定部门或任何子部门。

这是员工表的简化版本

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

还有部门表。 parentID 只是指同一张表中的其他部门。

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

下面是我整理的 CTE。只要在部门层次结构的每个级别中至少有一名员工，它将返回从顶部开始的所有员工的完整列表。但问题是，如果层次结构中有一个没有员工的部门，它会破坏那里的递归。

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

我正在尝试修复它，以便它将沿着整个部门层次结构向下移动，并且仅在没有找到子部门时终止，而不是在到达没有员工的部门时终止。

Answer 1

如wildplasser已经评论过，在 CTE 中获取没有员工的部门，然后在外部查询中加入它们。

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