Re: Nested Coalescing possible in SQL?

From: Jeff Lanfield <>
Date: 2 Jun 2004 11:38:21 -0700
Message-ID: <>

> 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
> /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
Received on Wed Jun 02 2004 - 20:38:21 CEST

Original text of this message