Re: Nested Coalescing possible in SQL?

jlanfield2003_at_yahoo.com (Jeff Lanfield) wrote in message news:<235c483f.0406011716.37d00399_at_posting.google.com>...
> First of all, I apologize if coalescing is not the right term to
> describe my problem. I have a tree where each node has the same set of
> attributes (is the same entity) but child nodes should inherit
> attribute values from parent node.
>
> for example, say I have the following table:
>
> (nodeId int , color varchar, phone varchar) with two rows
>
> 5, "GREEN", "555-1212"
> 7, NULL, "777-5555"
> 8, NULL, NULL
> 9, "BLUE", NULL
>
> in addition there is a tree structure that specifies that node 5 is
> the parent of node 7, and 7 is the parent of nodes 8 and 9. I know
> there is many ways to make trees in SQL but as a simple example let's
> say the tree is:ancestor a
>
> id, parentid
> 8, 7
> 9, 7
> 7, 5
>
> Thus in this case, node 7 inherits the value "GREEN" from node 5 for
> attribute "color", but provides its own value "777-5555" for attribute
> "phone". Node 8, in turn, inherits "GREEN" for "color" from node 7
> (really from node 5 since 7 did not specify its own) and "777-5555"
> for "phone" from node 7. Node 9 provides its own value for "color" and
> inherits the one for "phone" from Node 7.
>
> So the runtime values in the application are:
>
> Node 5: "GREEN", "555-1212"
> Node 7: "GREEN", "777-5555"
> Node 8: "GREEN", "777-5555"
> Node 9: "BLUE", "777-5555"
>
> Question 1: Is there a single SQL statement that for a given node can
> replace the NULLs with inherited values from the parent node?
>
> Question 2: Is there a better way to structure such data in SQL as to
> make answer to question 1 possible?
>
> I would restate the problem as follows:
>
> In a nested structure child nodes inherit values from parent nodes _by
> reference_ or specify their own. "By reference" is the key word here.
> If it wasn't for that you could just duplicate the necessary values
> from the parent entitity upon creation.
>
> Thanks!
>
> - Jeff

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 Received on Wed Jun 02 2004 - 14:10:31 CEST