Nested Coalescing possible in SQL?
Date: 1 Jun 2004 18:16:50 -0700
Message-ID: <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.Thanks!
- Jeff