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](https://i.stack.imgur.com/fLBv8.jpg)