Re: Nested Coalescing possible in SQL?
Date: 2 Jun 2004 11:38:21 -0700
Message-ID: <235c483f.0406021038.13a1b83b_at_posting.google.com>
> 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)
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 ...
Can such a query be constructed?
5, 0,"GREEN", "555-1212"
7, 5, NULL, "777-5555"
8, 7 NULL, NULL
9, 7 "BLUE", NULL
5,"GREEN", "555-1212"
7,"GREEN", "777-5555"
8,"GREEN", "777-5555"
9,"BLUE", "777-5555"
- Jeff