CODEWITHSUNDEEP
sql-servercommon-table-expressionrecursive-query
Catalin
Catalin

Reputation: 11721

Common Table Expression basic example

I have a recursive tree database table

DataItem

Id (uniqueidentifier)
Parent_Id? (uniqueidentifier)
PositionInParent (int)

I've read some articles about Common Table Expressions, which allows me to recursively read the tree structure directly from SQL database, but all of them are very complicated and i cannot make them work.

I am trying to read recursively all the DataItems, starting from the root ones (which has no parent), and adding the children items (ordered by PositionInParent)

Please help me create this simple example, and from there i will add more logic if necessary.

Upvotes: 0

Views: 87

Answers (1)

Faisal.lh
Faisal.lh

Reputation: 29

;WITH HierarchyCTE (ParentId, Id, Level)
AS
(
    SELECT e.ParentId, e.Id, 0 AS Level
    FROM Employees AS e
    WHERE ParentId IS NULL
    UNION ALL
    SELECT e.ParentId, e.Id, Level + 1
    FROM Employees AS e
    INNER JOIN HierarchyCTE AS h
        ON e.ParentId = h.Id
)
SELECT ParentId, Id, Level AS PositionInParent
FROM HierarchyCTE

You can use condition WHERE ParentId = 0if ParentId of super parent is 0

Upvotes: 1

Related Questions