18

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.

Improve this question

5 Answers 5

Reset to default
27 
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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This is the way I'd do it, recursing Down the tree from the node being searched from.
This works great! seems to be fast and accomplish just what I want. Thanks very much for the help! Finally I get somewhere again :)
@Dems +1. Recursing down is the way I want! :)
6

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.

Improve this answer

3 Comments

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?
@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.
Apologies, mis-read it, this does indeed recurse Down the tree, and so the WHERE clause does indeed prevent uneccesary calcualtion.
4

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
Improve this answer

Comments

0

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.

Improve this answer

1 Comment

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).
0

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.