programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
• Campbell Ritchie
• Ron McLeod
• Paul Clapham
• Devaka Cooray
• Liutauras Vilda
Sheriffs:
• Jeanne Boyarsky
• paul wheaton
• Henry Wong
Saloon Keepers:
• Stephan van Hulst
• Tim Holloway
• Tim Moores
• Carey Brown
• Mikalai Zaikin
Bartenders:
• Lou Hamers
• Piet Souris
• Frits Walraven

Tree Stored Procedure

Ranch Hand
Posts: 167
• Number of slices to send:
Optional 'thank-you' note:
Hi,

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.)

 A lot of people cry when they cut onions. The trick is not to form an emotional bond. This tiny ad told me: We need your help - Coderanch server fundraiser https://coderanch.com/wiki/782867/Coderanch-server-fundraiser