Recursive SQL Query for Employee Hierarchy Across Multiple Tables

Data and Databases
1

I’m trying to figure out how to create a recursive SQL query on SQL Server that retrieves the hierarchy of employees under a specific manager. I have five interconnected tables: Department, Role, DeptRole, Biography, and Employee. When I run the query with a condition such as WHERE EmpId = 1, my expectation is to see all subordinate employees, for instance, EmpId 5 followed by EmpId 4. Similarly, executing the query with EmpId = 2 should present the chain EmpId 3-1-5-4. My challenge is to write this recursive query, which effectively connects all these tables and accurately traverses the hierarchy. Any sample code or guidance on setting up such a recursive Common Table Expression (CTE) would be really appreciated.

-- Example of a simplified table structure
CREATE TABLE Department (id INT, dept VARCHAR(50), parent INT);
CREATE TABLE Role (id INT, role VARCHAR(50), parent INT);
CREATE TABLE DeptRole (id INT, iddept INT, idrole INT);
CREATE TABLE Biography (id INT, name VARCHAR(50), regnumber VARCHAR(10));
CREATE TABLE Employee (id INT, IdBio INT, deptrole INT);

-- Example of a recursive CTE structure
WITH RecursiveHierarchy AS (
    -- Base case: start with the given employee
    SELECT id, IdBio, deptrole FROM Employee WHERE id = @EmpId
    UNION ALL
    -- Recursive member: join to find subordinate employees
    SELECT e.id, e.IdBio, e.deptrole FROM Employee e
    JOIN RecursiveHierarchy r ON e.deptrole = r.id
)
SELECT * FROM RecursiveHierarchy;
2

I’ve tackled similar hierarchical queries before, and here’s an approach that might work for your scenario. Consider using a combination of CTEs to handle the relationships between your tables. Start with a CTE for the employee hierarchy, then join it with the other tables to get the full picture. Here’s a rough outline:

WITH EmployeeHierarchy AS (
SELECT id, IdBio, deptrole, 0 AS Level
FROM Employee
WHERE id = @EmpId
UNION ALL
SELECT e.id, e.IdBio, e.deptrole, eh.Level + 1
FROM Employee e
JOIN EmployeeHierarchy eh ON e.deptrole = eh.id
),
FullHierarchy AS (
SELECT eh.*, d.dept, r.role, b.name
FROM EmployeeHierarchy eh
JOIN DeptRole dr ON eh.deptrole = dr.id
JOIN Department d ON dr.iddept = d.id
JOIN Role r ON dr.idrole = r.id
JOIN Biography b ON eh.IdBio = b.id
)
SELECT * FROM FullHierarchy
ORDER BY Level;

This approach should give you a comprehensive view of the employee hierarchy while including relevant information from all tables. Adjust as needed for your specific requirements.

3

ooh, interesting problem! have you tried using a self-join in your CTE? that might help track the hierarchy better. also, how are you handling cases where an employee has multiple roles or departments? that could get tricky. maybe we could brainstorm some ideas to tackle those edge cases?

4

hey ryan, have u considered using multiple CTEs? one for each table might simplify things. then u could join them in the final query. something like:

WITH DeptCTE AS (...),
     RoleCTE AS (...),
     EmpCTE AS (...)
SELECT ...
FROM EmpCTE e
JOIN DeptCTE d ON ...
JOIN RoleCTE r ON ...

just a thought. good luck!