Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    india
    Posts
    2

    Unanswered: Hierarchical Tree Structures in Database Tables

    I want to make a Hierarchical Tree Structures in Database Tables

    suppose there is a table

    Tree(ID int, Name Varchar(40), and Parent_id int)

    and table structure look like this

    ID Name Parent_id
    1 A1 -1
    2 A1.1 1
    3 A1.2 1
    4 A1.1.1 2
    5 A1.1.2 2
    6 A1.2.1 3
    7 A1.2.2 3
    8 A1.1.1.1 4
    9 A1.2.2.1 7


    so we can say

    A1 --|---A1.1--|------A1.1.1-----------A1.1.1.1
    | |------A1.1.2
    |
    |---A1.2----|----A1.2.1
    |----A1.2.2------------A1.2.2.1


    This type of tree structure is made by table


    So when i say Tree where root is 1 i will get whole table(above)

    when i say tree where root is 2
    then i will get

    2 A1.1 1
    4 A1.1.1 2
    5 A1.1.2 2
    8 A1.1.1.1 4

    can i do this without using ant Stored proc and function

    plz help regarding this
    thanks in advance
    plz also mail me on viren_balaut?@yahoo.co.in
    viren.balaut@gmail.com
    Viren Balaut

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Use the CONNECT BY clause. Also the later version of Oracle like 9i and 10g have enhanced functionality like functions to list the full path (i.e. A1.1.1.1) and detection of circular references etc.

    select lpad(' ',2*(level-1)) || to_char(id) s
    from <table>
    start with parent_id = -1
    connect by prior id = parent_id;


    Alan
    Last edited by AlanP; 02-07-05 at 07:09. Reason: erorr with parent_id

  3. #3
    Join Date
    Nov 2003
    Location
    india
    Posts
    2

    Re:-Hierarchical Tree Structures in Database Tables

    Hi Alan
    Could u give some more information regarding this

    if u can that information is very helpful for me
    suppose i have a table

    Create table Test(
    id number,
    name varchar2(40),
    p_id number);

    and values are 1, a,-1
    2,a.1,1
    3,a.2,1
    4,a.1.1,2
    5,a.1.2,2
    ....

    like this so how i will get this

    thanks in advance

    viren

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •