Re: Nested Coalescing possible in SQL?

"Jeff Lanfield" <jlanfield2003_at_yahoo.com> 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:
>
> 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.

It looks easy. Find a closest node in the chain of ancestors that has property not NULL. Received on Tue Jun 01 2004 - 20:35:17 CDT