Win a copy of Testing JavaScript Applications this week in the HTML Pages with CSS and JavaScript forum!
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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
• Campbell Ritchie
• Bear Bibeault
• Ron McLeod
• Jeanne Boyarsky
• Paul Clapham
Sheriffs:
• Tim Cooke
• Liutauras Vilda
• Junilu Lacar
Saloon Keepers:
• Tim Moores
• Stephan van Hulst
• Tim Holloway
• fred rosenberger
• salvin francis
Bartenders:
• Piet Souris
• Frits Walraven
• Carey Brown

Tree Stored Procedure

Ranch Hand
Posts: 167
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.)

 Consider Paul's rocket mass heater.