This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes Websphere and the fly likes Tree Stored Procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Products » Websphere
Bookmark "Tree Stored Procedure" Watch "Tree Stored Procedure" New topic

Tree Stored Procedure

Sharad Agarwal
Ranch Hand

Joined: Sep 11, 2002
Posts: 167

We have three tables A, B and C which represent a tree like structure. Each row in A can have zero or more children, each represented by rows in B. Similarly each row in B can have zero or more children, each represented by rows in C.

To illustrate, here is an example. There are three nodes (rows) in A - A1, A2 and A3. A1 has two child nodes in B - B1 and B2. A2 has no children. A3 had children B3 and B4. B1 has two children C1 and C2. B2 has children C3, C4 and C5. B3 has no children. B4 has 3 children - C6, C7 and C8.

Now we need a stored procedure that, given the primary key for a row in A, returns the sub-tree for that node. So, for example, if you are given A1, you need to return the subtree under that node.

I was looking for suggestions for the data structure returned. A simple data structure could be one that flattens out the tree:

A1 B1 C1
A1 B1 C2
A1 B2 C3
A1 B2 C4
A1 B2 C5

It leads to some redundancy. But if there are not too many siblings and the data is small, it is a simple solution. Or I could return three structures with only the needed attributes.

Other ideas?

(Note - the situation and example presented here are simplifications that focus on the issue I am struggling with.)

Alco-Haul: We move spirits.
Demented Deliberations of a Dilettante
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
subject: Tree Stored Procedure