CODEWITHSUNDEEP
sqlsql-server
Namek
Namek

Reputation: 487

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

I'm looking for a way, to query whole tree structure (including top parent, to end bottom child), base on querying based on top parent.

BUT! It should return: - strucuture (more than one element), - or one element only (top level structure, if there is no childs below - but in this case, only if there is nothing "higher" - the ParentID IS NULL in this case).

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     |
|              |
|305 | NULL    |
----------------

Based on querying for 229, the result should be:

229
230
231
232
233

Based on querying for 230, or 231, or 232, or 233, the result should be empty!

Based on querying for 300, the result should be:

300
301
302
303
304

Based on querying for 301, the result should be:

301
302
303

Based on querying for 302, the result should be:

302
303

Based on querying for 303, or 304, the result should be empty!

Based on querying for 305, the result should be:

305

Upvotes: 0

Views: 83

Answers (1)

Andrey Nikolov
Andrey Nikolov

Reputation: 13450

Modifying my answer from your previous question to match the new requirements. You need to add a check in the recursive CTE when starting to traverse the hierarchy, to see is this a parent node or are there any children of 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, 302 ),
(304, 300 ),

(305, NULL )

declare @LookingForId int = 305
; with cte as (
  select ID from @Table t1 where t1.ID = @LookingForId and (t1.ParentID is null or exists (select * from @Table t2 where t2.ParentID = t1.ID))
  union all
  select t.ID from @Table t inner join cte on cte.ID = t.ParentID
)
select * from cte

Upvotes: 1

Related Questions