Recursive select in SQL

Question

I have an issue I just can't get my head around. I know what I want, just simply can't get it out on the screen. What I have is a table looking like this:

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

ParentId have a FK to Id.

What I want to accomplish is to get a flat list of all the id's below the Id I pass in.

example:

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

The tree would look like this:

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

If I now ask for 4, I would only get 4 back, but if I ask for 1 I would 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 help I can get.

Cacka

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

Boule

This is the way I'd do it, recursing Down the tree from the node being searched from. – Salivate

This works great! seems to be fast and accomplish just what I want. Thanks very much for the help! Finally I get somewhere again :) – Cacka

@Dems +1. Recursing down is the way I want! :) – Cacka

Answer 2

Here's 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    @t t
        union all
        select  t.name
        ,       r.baseid
        ,       t.id
        ,       t.parentid
        from    rec r
        join    @t t
        on      t.ParentID = r.id
        )
select  *
from    rec
where   baseid = 1

You can filter on baseid, which contains the start of the tree you're querying for.

Briolette

This recurses up the tree, for the whole tree, then filters for the wanted records. Is it not 'better' to start at the required node and then recurse down? – Salivate

@Dems: The optimizer will move the where clause into the CTE, so it'll execute as you suggest. It seemed clearer to move the start condition outside the recursive machinery. – Briolette

Apologies, mis-read it, this does indeed recurse Down the tree, and so the WHERE clause does indeed prevent uneccesary calcualtion. – Salivate

Answer 3

Try this:

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

Carner

Answer 4

Here is a good article about Hierarchy ID models. It goes right from the start of the data right through to the query designs.

Also, you could use a Recursive Query using a Common Table Expression.

Stile

It's worth mentioning that the first article you link to actually uses Common Table Expressions for recursive queries (even though it doesn't come out and mention the fact). – Ripen

Answer 5

I'm guessing that the easiest way to accomplish what you're looking for would be to write a recursive query using a Common Table Expression:

MSDN - Recursive Queries Using Common Table Expressions

Ripen