Finding the Organization Hierarchy using SQL Server

In SQL Server, retrieving organizational hierarchy data often involves querying a table that stores hierarchical relationships. This is commonly achieved using one of several methods, including recursive Common Table Expressions (CTEs), adjacency list models, or nested set models. Here's an overview of how you can approach each method.

1. Adjacency List Model

In this model, you typically have a table where each row includes a reference to its parent row. For example.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    ManagerID INT,
    FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);

To find the hierarchy, you can use a recursive CTE. Here’s an example of how to retrieve the hierarchy of employees.

WITH EmployeeHierarchy AS (
    -- Anchor member: start with top-level employees (those with no manager)
    SELECT 
        EmployeeID,
        Name,
        ManagerID,
        1 AS Level -- Root level
    FROM Employees
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- Recursive member: join the hierarchy with itself to get child employees
    SELECT 
        e.EmployeeID,
        e.Name,
        e.ManagerID,
        eh.Level + 1 AS Level
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh
    ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy
ORDER BY Level, ManagerID, EmployeeID;

2. Nested Set Model

In this model, you store hierarchical data using left and right values that define the position of nodes in the hierarchy. Here’s an example table.

CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    CategoryName NVARCHAR(100),
    LeftValue INT,
    RightValue INT
);

To retrieve the hierarchy, you would perform a self-join.

SELECT 
    parent.CategoryName AS ParentCategory,
    child.CategoryName AS ChildCategory
FROM Categories parent
INNER JOIN Categories child
ON child.LeftValue BETWEEN parent.LeftValue AND parent.RightValue
WHERE parent.LeftValue < child.LeftValue
ORDER BY parent.LeftValue, child.LeftValue;

3. Path Enumeration Model

In this model, each row stores the path to its root. For example.

CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    CategoryName NVARCHAR(100),
    Path NVARCHAR(MAX)
);

To get the hierarchy, you can query the Path field. Here’s a simple example of getting all descendants of a given node.

DECLARE @CategoryID INT = 1; -- Assuming the root node has CategoryID 1
SELECT *
FROM Categories
WHERE Path LIKE (SELECT Path FROM Categories WHERE CategoryID = @CategoryID) + '%';

Summary

  • Adjacency List Model: Uses a ManagerID column to establish parent-child relationships. Recursive CTEs are commonly used to traverse the hierarchy.
  • Nested Set Model: Uses LeftValue and RightValue columns to represent hierarchical relationships. Efficient for read-heavy operations.
  • Path Enumeration Model: Stores the path to the root, making it easy to query descendants and ancestors.

The choice of model depends on your specific needs and the nature of your hierarchical data.