• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

Hierarchical Structure

There are numerous example of how to store the hierarchical tree structure is in SQL 200 DB, and probably the most pointed out is the Celko's method (http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=6477). To tell you the truth I'm having a hard time wrapping my mind around it and I do need some help getting it to work (does not have to be Celko in my case either). I'm using SQL 2K and .NET (VB). First what I need it for:

I was asked to create a "troubleshooter" type of app for our internal use, where you select one question and it gives you a list of other related questions from where you select another question and so on until you reach your solution.

For my needs I the tree does not need to go deeper then say 25 levels. Though infinite tree would be nice too :)

So the 5 things I need is

- the structure used to save the relations (including the question content obviously I'll add my fields here)
- a query or SP that would list available parent in this format:
P1 > Sub-P1 > Sub-P2
P2 > Sub-P2 > Sub-P3 > Sub-P4
(This would get loaded to a control from where you'll select the paretn for a new question)
- a query to add the new Q if the insert is somewhat more difficult then referencing the parent ID
- a way to delete any question including it's sub-questions.
- ideally the information would be stored in one table, but if not possible then I can live with it :)

I won't need the ability to move one question under another, so the nodes will stay in the same place once it's added. I know I'm asking a lot questions that require a lot of code and I don't expect a complete solution, but rather a few example how it can be accomplished.

Any ideas are welcomed ! Thanks.

1 Solution
Melih SARICAOwnerCommented:
what u want is like an Bill of Material tree

its something like that
u must have an list of all items... and then u must create relations between these items

MainItem SubItem
----------- ---------
1              2
1              3
1              4
2              5
2              6
2              7
3              5
4              7
5              8
5              9

how ll u query this structure?

for example u ave a question ...
 number 1

query mainitem 1
u get 3 results  ( 2- 3 - 4)

then u see that item 2 is ur near ur solution

then u ll query mainitem 2
 then u ll requery main item with selected sub item till u ave no rows returned..

it ll be ur Solution

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now