Namek
Namek

Reputation: 487

How to get self-referenced table tree structure, based on any node in tree?

I'm looking for a way, to query whole tree structure (including top parent, to end bottom child), base on any node Id - no matter, by querying based on top parent, any node inside, or very bottom child.

I.e. having table with:

|--------------|
|ID  | ParentID|
|--------------|
|229 | NULL    |
|230 | 229     |
|231 | 229     |
|232 | 229     |
|233 | 229     |
|              |
|300 | NULL    |
|301 | 300     |
|302 | 301     |
|303 | 302     |
|304 | 300     |
----------------

Based on querying for 229, or for 230, or for 231, or for 232, or for 233, the result should be always the same:

229
230
231
232
233

Based on querying for 300, or for 301, or for 302, or for 303, or for 304, the result should be always the same:

300
301
302
303
304

Upvotes: 0

Views: 608

Answers (3)

Zeki Gumus
Zeki Gumus

Reputation: 1484

I think you need something like that :

SELECT  Id 
FROM TableName AS T
WHERE ISNULL(ParentId,Id) IN 
    (
        SELECT ISNULL(ParentId,Id)
        FROM TableName WHERE Id=229
    )
ORDER BY T.Id

Upvotes: 1

Andrey Nikolov
Andrey Nikolov

Reputation: 13450

What you need is a recursive common table expression (CTE).

Here is how to query it:

declare @Table table(ID int, ParentID int)

insert into @Table(ID, ParentID) values
(229, NULL),
(230, 229 ),
(231, 229 ),
(232, 229 ),
(233, 229 ),

(300, NULL),
(301, 300 ),
(302, 301 ),
(303, 300 )

declare @LookingForId int = 300 --229
; with cte as (
  select ID from @Table where ID = @LookingForId
  union all
  select t.ID from @Table t inner join cte on cte.ID = t.ParentID
)
select * from cte

Common table expressions are like temporary view, existing only in your query. Recursive CTEs are those, which reference themselves in their body. You can use this technique to traverse the parent-child hierarchy in your table.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can assign each id to its ultimate parent using a recursive CTE. Then, you can use this information to get all ids from the parent:

with t as (
      select v.*
      from (values (229, null), (230, 229), (231, 229), (232, 229), (233, 229), 
                   (300, NULL), (301, 300), (302, 301), (303, 300 )
           ) v(id, parentid)
     ),
     cte as (
      select id as ultimate_parent_id, id as relatedid
      from t
      where parentid is null
      union all
      select cte.ultimate_parent_id, t.id
      from cte join
           t
           on cte.relatedid = t.parentid
     )
select relatedid
from cte
where ultimate_parent_id = (select ultimate_parent_id
                            from cte
                            where relatedid = 231
                           );

Here is a db<>fiddle.

Upvotes: 4

Related Questions