John-David Bennett John-David Bennett - 7 months ago 19
SQL Question

Select all hierarchy level and below sql server

I am having a difficult time with this one. I have seen a few examples on how to obtain all child records from a self referencing table given a parent and even how to get the parents of child records.

What I am trying to do is return a record and all child records given the ID.

To put this into context - I have a corporate hierarchy. Where:

#Role Level#
Corporate 0
Region 1
District 2
Rep` 3`


What I need is a procedure that (1) figures out what level the record is and (2) retrieves that record and all children records.

The idea being a Region can see all districts and reps in a district, Districts can see their reps. Reps can only see themselves.

I have table:

ID ParentId Name
1 Null Corporate HQ
2 1 South Region
3 1 North Region
4 1 East Region
5 1 West Region
6 3 Chicago District
7 3 Milwaukee District
8 3 Minneapolis District
9 6 Gold Coast Dealer
10 6 Blue Island Dealer


How do I do this:

CREATE PROCEDURE GetPositions
@id int
AS
BEGIN
--What is the most efficient way to do this--
END
GO


For example the expected result for @id = 3, I would want:

3,6,7,8,9,10 returned.

I'd appreciate any help or ideas on this.

Answer

You could do this via a recursive CTE:

DECLARE @id INT = 3;

WITH rCTE AS(
    SELECT *, 0 AS Level FROM tbl WHERE Id = @id
    UNION ALL
    SELECT t.*, r.Level + 1 AS Level
    FROM tbl t
    INNER JOIN rCTE r
        ON t.ParentId = r.ID
)
SELECT * FROM rCTE OPTION(MAXRECURSION 0);

ONLINE DEMO

Comments