How to do a recursive join to get the lowest level of data with TSQL

Question

I have the following set of data:

ID          ParentID 
----------- ---------
8320        NULL     
8321        8320     
8322        8320     
8323        8322     
8325        NULL     
8328        8325     
8329        8328 

What I am trying to achieve is to select all the rows that belongs to a specific ID. For instance, if I am querying ID = 8320, the following data must be returned:

ID          ParentID 
----------- ---------
8320        NULL     
8321        8320     
8322        8320     
8323        8322

So far this is what I have attempted with no real success.

select *
from JobQueueLog JQL
    left join JobQueueLog JQLC on
        JQL.ID = JQLC.ParentID
    and JQLC.ParentID is not null
where JQL.ID = 8320

Any help please?

Answer 1

You need to use a CTE and make a recursive query

with tmp (id, parentid) as (
select id, parentid
from rec
where id = 8320
union all
select rec.id, rec.parentid
from tmp
inner join rec on tmp.id = rec.parentid
)
select id, parentid
from tmp