Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2005
    Posts
    29

    Unanswered: hiearchical list of tree

    Hi,

    table(id,parent,level, name)
    parent ...fk referenced table(id))
    level ... depth of tree (root has 0)

    How SQL for hiearchical list:

    name 1
    name 1.1
    name 1.2
    name 2.
    name 2.1.
    name 2.1.1
    name 2.1.2
    etc.

    ???
    thanks for your help

  2. #2
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Please post the complete DDL for your table, some sample data, and the desired result. That, I might be able to help with.

  3. #3
    Join Date
    Jul 2005
    Posts
    29
    create table A(
    id int primary key not null,
    parent int null, --parent of the row
    level int null, --position in DB tree from root
    name varchar (20)
    )

    alter table A add
    constraint fk foreign key(parent) references A(id)

    insert into A(id,parent,level,name) values (1,null,0,'name 1.');
    insert into A(id,parent,level,name) values (2,1,1,'name 1.1.');
    insert into A(id,parent,level,name) values (3,1,1,'name 1.3.');
    insert into A(id,parent,level,name) values (4,2,2,'name 1.1.1.');
    insert into A(id,parent,level,name) values (5,1,1,'name 1.2.');
    insert into A(id,parent,level,name) values (6,3,2,'name 1.3.1.');
    ...

    I'd like to display :
    name 1.
    name 1.1.1.
    name 1.2.
    name 1.3.
    name 1.3.1.
    ...

    name 1.
    name 1.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this will do it...
    Code:
    select name from A order by name
    i know it's not the general solution, but it is a prefectly good solution to the particular data that you have, with the enumerated path included as part of the name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2005
    Posts
    29
    thanks, but else, not order name
    Column a.name contains 'sdfdasasd' etc too.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  7. #7
    Join Date
    Jul 2005
    Posts
    29
    I'd like to avoid a recursion solution. How ways are possible?
    DDL can be altered.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how deep is the tree? how many levels?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2005
    Posts
    29
    more than 15 levels

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my condolences, as that would be phenomenally difficult for most users to navigate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by Perseus
    I'd like to avoid a recursion solution.
    Why? Recursion would be the easiest method, especially if the number of levels isn't fixed.

  12. #12
    Join Date
    Jul 2005
    Posts
    29
    because a recursion should be time difficult

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you may not want to hear this, but any solution for the adjacency model going down 15+ levels will be "time difficult"

    have you thought about a different data design?

    do a search for joe celko's nested set model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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