Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2010
    Posts
    3

    Traverse self-referencing table

    Hello.

    I have a self-referencing table like

    column
    id
    description
    parent_id

    any know how can I Traverse the hierarchy table from a given id? thx

    yunaki

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Using standard SQL, this can be solved using a recursive common table expression:
    Code:
    with traverse_tree (id, description, parent_id)  as 
    (
       SELECT id, description, parent_id 
       FROM the_table_with_no_name
       WHERE id = 42
       
       UNION ALL
    
       SELECT t.id, t.description, t.parent_id 
       FROM the_table_with_no_name t
         JOIN traverse_tree p ON t.parent_id = p.id
    )
    SELECT *
    FROM traverse_tree

  3. #3
    Join Date
    Aug 2010
    Posts
    3

    thanks answer.

    I am a mysql user. does Mysql support "with" statement? I remember it did not. any idea in Mysql?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by yunaki View Post
    any idea in Mysql?
    mysql does not support common table expressions (CTEs) and i do not know whether or when it might do so

    you might want to consider a query which has as many LEFT OUTER JOINs as there are levels in the hierarchy

    see Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Quote Originally Posted by yunaki View Post
    I am a mysql user. does Mysql support "with" statement? I remember it did not. any idea in Mysql?
    If you are using a specific DBMS, then you should post in that forum. I wouldn't have posted that answer in the MySQL forum...

Posting Permissions

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