Hierarchical Structure

Posted on 2005-05-08
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 ( 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

    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

    Melih SARICA

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now