[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Hierarchical Structure

Posted on 2005-05-08
Medium Priority
Last Modified: 2010-03-19

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.

Question by:vpekulas
1 Comment
LVL 19

Accepted Solution

Melih SARICA earned 2000 total points
ID: 13955588
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


Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question