Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Nested Coalescing possible in SQL?

Re: Nested Coalescing possible in SQL?

From: Jeff Lanfield <jlanfield2003_at_yahoo.com>
Date: 2 Jun 2004 11:38:21 -0700
Message-ID: <235c483f.0406021038.13a1b83b@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)  

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?

Received on Wed Jun 02 2004 - 13:38:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US