Reputation: 487
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
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
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
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