Rudolf Lamprecht Rudolf Lamprecht - 1 month ago 9
SQL Question

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

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

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