How to Retrieve a Category and Its Ancestors Using Recursive Self-Join in SQL Server?

Using Recursive Self-Joins in SQL Server to Retrieve Categories and Their Ancestors

This article demonstrates how to retrieve a category and its ancestor categories from a hierarchical table in SQL Server using a recursive self-join. We'll use a Common Table Expression (CTE) for this task.

Scenario:

Consider a Categories table with Id, Name, and ParentId columns representing a hierarchical category structure. The goal is to write a query that returns a specific category (e.g., "Business Laptops") along with all its parent categories in a single, comma-separated string.

Recursive CTE Solution:

The solution employs a recursive CTE to traverse the hierarchical structure. Here's the query:

<code class="language-sql">WITH CategoryHierarchy AS (
    SELECT id, name, CAST(name AS VARCHAR(MAX)) AS path, parent_id
    FROM Categories
    WHERE parent_id IS NULL  -- Start with root categories
    UNION ALL
    SELECT c.id, c.name, CAST(ch.path + ',' + c.name AS VARCHAR(MAX)), c.parent_id
    FROM Categories c
    INNER JOIN CategoryHierarchy ch ON c.parent_id = ch.id
)</code>

This CTE, CategoryHierarchy, recursively joins the Categories table to itself. The initial SELECT statement selects root categories (those with parent_id as NULL). The UNION ALL combines this with subsequent recursive selections, building the path string by concatenating parent and child category names.

Query to Retrieve Specific Category and Ancestors:

To retrieve the "Business Laptops" category and its ancestors:

<code class="language-sql">SELECT id, name, path
FROM CategoryHierarchy
WHERE name = 'Business Laptops';</code>

Example Table and Data:

Let's create a sample Categories table and insert some data:

<code class="language-sql">CREATE TABLE Categories (
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
    ParentId INT REFERENCES Categories(Id)
);

INSERT INTO Categories (Id, Name, ParentId)
VALUES
(1, 'Electronics', NULL),
(2, 'Laptops', 1),
(3, 'Desktops', 1),
(4, 'Business Laptops', 2),
(5, 'Gaming Laptops', 2);</code>

Expected Result:

For the query targeting 'Business Laptops', the expected output would be:

<code>id      name                path
4       Business Laptops     Electronics,Laptops,Business Laptops</code>

This approach effectively retrieves a category and its complete lineage using a recursive CTE, providing a clear and efficient solution for navigating hierarchical data in SQL Server. Remember to adjust the VARCHAR(MAX) length if your category names might exceed this limit.

The above is the detailed content of How to Retrieve a Category and Its Ancestors Using Recursive Self-Join in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!