Traverse self-referencing table

yunaki · #1 (**permalink**) 08-23-10, 06:57

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

shammat · #2 (**permalink**) 08-23-10, 07:05

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

yunaki · #3 (**permalink**) 08-23-10, 17:20

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

r937 · #4 (**permalink**) 08-23-10, 18:11

Quote:

Originally Posted by yunaki

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