Tree structure in SQL Server 2008

Question

In SQL Server 2008;

I have a tree. I need to get all child nodes of node n (see diagram) and all child nodes of these child nodes, etc until the leaf nodes which is fairly trivial. I also need to be able to say 'Take node o, go up the tree until we reach m and because m is a child of node n set some property of node o to some property of node m. Node o could be 3 levels deep (as illustrated) or 45 levels deep, x levels deep.

This gets all children of a given node (or area)

    --Return all sub-area structure of an area:

      WITH temp_areas (ParentAreaID, AreaID, [Name], [Level]) AS
      (
            SELECT ParentAreaID, AreaID, [Name], 0
            FROM lib_areas WHERE AreaID = @AreaID
            UNION ALL
            SELECT B.ParentAreaID, B.AreaID, B.[Name], A.Level + 1
            FROM temp_areas AS A, lib_areas AS B
            WHERE A.AreaID = B.ParentAreaID
      )

INSERT INTO @files (id) SELECT fileid FROM lib_filesareasxref where areaid in (select  areaid from temp_areas)

while exists (select * from @files)
begin
select top 1
@ID = id
from
@files ORDER BY id DESC

delete from @files where id = @id

tree

Is your question "Does this make sense?" or is there more to it than that? — podiluska
– podiluska, Commented Jul 6, 2012 at 9:03
Please show the results you expect for your example. Such as, do you need to know the depth of each node? Or the depth relative to m, or the depth relative to o? If you define your output we can demonstrate the code. — MatBailie
– MatBailie, Commented Jul 6, 2012 at 9:06
Okay, I will show sample results. I don't need to know the depth of each node, no. — Sam Leach
– Sam Leach, Commented Jul 6, 2012 at 9:08
@podiluska, no that's not my question. Sorry for not being clear, I was just making sure that my explanation of the problem was clear as I had doubts if it was or not. My question is how to implement the behavior I described (with nodes n,m and o). — Sam Leach
– Sam Leach, Commented Jul 6, 2012 at 9:17

MatBailie · Accepted Answer · 2012-07-06 09:11:00Z

2

This will track back from @node_o until it reaches @node_m or it reaches the top of the tree (if @node_m is not above @node_o).

  WITH
    parents
  AS
  (
    SELECT
      A.ParentAreaID, A.AreaID, A.[Name], 0
    FROM
      lib_areas  AS A
    WHERE
      A.AreaID = @node_o

    UNION ALL

    SELECT
      A.ParentAreaID, A.AreaID, A.[Name], B.Level + 1
    FROM
      lib_areas  AS A
    INNER JOIN
      parents    AS B
        ON A.AreaID = B.ParentAreaID
    WHERE
      B.AreaID <> @node_m
  )
  SELECT
    *
  FROM
    parents

answered Jul 6, 2012 at 9:11

MatBailie

87.4k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

podiluska · Accepted Answer · 2012-07-06 09:18:11Z

0

I'd propose using a HierarchyID data type in your table, and using the GetAncestor method

answered Jul 6, 2012 at 9:18

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

2 Comments

Sam Leach Over a year ago

This looks really nice but I it's not feasible to create a new table or modify the existing one in this instance.

podiluska Over a year ago

Shame! In that case, @Dems common table expression is probably the way to go

Collectives™ on Stack Overflow

Tree structure in SQL Server 2008

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related