Re: Nested Coalescing possible in SQL?

> If you would like to do it in a single query, you will have to extend
> your tree, or if your db supports it, you can use recursion. There are
> several ways of extending your tree, nested set, transitive closure,
> etc. If you google comp.database and comp.database.theory you will
> find several threads regarding this.
>
> Assuming you can "calculate" the set of ancestors for any given node,
> define the suspects as "ancestors with property p". The property you
> are looking for can be found in the suspect with the largest depth*.
>
> *depth = number of ancestors
>
>
> HTH
> /Lennart

So say the tree is specified like this:

(nodeId int, parentId int, color varchar, phone varchar)  

5, 0,"GREEN", "555-1212"
7, 5, NULL,   "777-5555"
8, 7  NULL,    NULL
9, 7 "BLUE",   NULL

That is: node 7 inherits values from 5. Nodes 8,9 inherit values from 7. Node 5 is the top level node.

I want to run a query that would give the following result set:

select nodeId, color, phone from ...  

5,"GREEN", "555-1212"
7,"GREEN", "777-5555"
8,"GREEN", "777-5555"
9,"BLUE",  "777-5555"
 

Can such a query be constructed?

• Jeff