Recursive selection in SQL

Question

I have a problem, I just can’t lower my head. I know what I want, I just can’t get it on the screen. I have a table that looks like this:

Id, PK UniqueIdentifier, NotNull Name, nvarchar(255), NotNull ParentId, UniqueIdentifier, Null 

ParentId has FK for identifier.

What I want to accomplish is to get a flat list of all identifiers below the identifier I was going through.

Example:

 1 TestName1 NULL 2 TestName2 1 3 TestName3 2 4 TestName4 NULL 5 TestName5 1 

The tree will look like this:

 -1 -> -2 -> -3 -> -5 -4 

If I ask for 4 now, I will return only 4, but if I ask for 1, I will get 1, 2, 3, and 5. If I ask for 2, I would get 2 and 3, and so on.

Is there anyone who can point me in the right direction. My brain is fried, so I appreciate all the help I can get.

Answer 1

 declare @T table( Id int primary key, Name nvarchar(255) not null, ParentId int) insert into @T values (1, 'TestName1', NULL), (2, 'TestName2', 1), (3, 'TestName3', 2), (4, 'TestName4', NULL), (5, 'TestName5', 1) declare @Id int = 1 ;with cte as ( select T.* from @T as T where T.Id = @Id union all select T.* from @T as T inner join cte as C on T.ParentId = C.Id ) select * from cte 

Result

 Id Name ParentId ----------- -------------------- ----------- 1 TestName1 NULL 2 TestName2 1 5 TestName5 1 3 TestName3 2 

Answer 2

Here is a working example:

 declare @t table (id int, name nvarchar(255), ParentID int) insert @t values (1, 'TestName1', NULL), (2, 'TestName2', 1 ), (3, 'TestName3', 2 ), (4, 'TestName4', NULL), (5, 'TestName5', 1 ); ; with rec as ( select t.name , t.id as baseid , t.id , t.parentid from @tt union all select t.name , r.baseid , t.id , t.parentid from rec r join @tt on t.ParentID = r.id ) select * from rec where baseid = 1 

You can filter by baseid , which contains the beginning of the tree you are requesting.

Answer 3

Here is a good article on Hierarchical Identity Models . It goes from the very beginning of the data right to the query constructs.

Alternatively, you can use a Recursive query using a shared table expression .

Answer 4

I assume that the easiest way to accomplish what you are looking for is to write a recursive query using the Common Table expression:

MSDN - Recursive Queries Using Common Table Expressions

Answer 5

Try the following:

 WITH RecQry AS ( SELECT * FROM MyTable UNION ALL SELECT a.* FROM MyTable a INNER JOIN RecQry b ON a.ParentID = b.Id ) SELECT * FROM RecQry