SQL - Multi level self join

Question

i have a table which has parent child relationship like this - Isfinal column suggests that it is the final element of that level

ID    name    ParentId    Isfinal
1     abc       0           No
2     acd       1           No
3     ads       1           No
4     xyz       2           No
5     xxy       2           Yes
6     plm       3           No
7     ytr       4           Yes
8     lks       6           Yes

I am trying to write a dynamic query which will give the child element of that ID.

E.G.

If I select 2 then it should give the result as -

ID    name    ParentId    Isfinal
4     xyz       2           No
5     xxy       2           Yes
7     ytr       4           Yes

Is it possible with self join?

Answer 1

Using a recursive CTE , you can solve this.

DECLARE @TABLE  TABLE
(   ID          int
    ,name       nvarchar(200)
    ,ParentId   int
    ,Isfinal    nvarchar(20)
)

INSERT INTO @TABLE
VALUES (1,'abc',0,'No'),(2,'acd',1,'No'),(3,'ads',1,'No'),
        (4,'xyz',2,'No'),(5,'xxy',2,'Yes'),(6,'plm',3,'No'),
        (7,'ytr',4,'Yes'),(8,'lks',6,'Yes')

DECLARE @ID INT = 2

;WITH CTE
AS
(
    SELECT  ID,name,ParentId,Isfinal
    FROM    @TABLE
    WHERE   ParentId    =   @ID

    UNION ALL

    SELECT  T.ID,T.name,T.ParentId,T.Isfinal
    FROM    @TABLE  T
        INNER JOIN  CTE C   ON  C.ID    =   T.ParentId
)

SELECT * FROM CTE