How Can Recursive Self-Joins in SQL Server Retrieve Hierarchical Data?

SQL Server Recursive Self-Joins: Navigating Hierarchical Data

SQL Server's recursive self-joins offer a powerful method for traversing hierarchical data structures. This is especially valuable when dealing with parent-child relationships within a single table.

Consider a Categories table with these columns:

• Id
• Name
• ParentId

A recursive self-join allows us to extract the hierarchical relationships within this table, encompassing nested category levels.

Illustrative Example:

Imagine this category hierarchy:

<code>- Business Laptops
    - Gaming Laptops
        - Ultrabooks
            - Compact Ultrabooks</code>

To retrieve "Business Laptops" and its parent categories using a recursive common table expression (CTE), we can use this query:

<code class="language-sql">;WITH CTE AS (
    SELECT id, name, name AS path, parent_id
    FROM Categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT t.id, t.name, CAST(cte.path + ',' + t.name AS VARCHAR(100)), t.parent_id
    FROM Categories t
    INNER JOIN CTE ON t.parent_id = CTE.id
)
SELECT id, name, path
FROM CTE
WHERE name = 'Business Laptops';</code>

This query's output would be:

<code>id      name                path
1       Business Laptops      Business Laptops</code>

Recursive self-joins provide an efficient way to manage and retrieve data from complex hierarchical structures, accessing related information across multiple levels.

The above is the detailed content of How Can Recursive Self-Joins in SQL Server Retrieve Hierarchical Data?. For more information, please follow other related articles on the PHP Chinese website!