How to do a MySQL recursive query?

how to work with recursive query in MySql?

• WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS ( SELECT a, b, 1 AS distance, a || '.' || b || '.' AS path_string, b AS direct_connection FROM edges2 WHERE a = 1 -- set the starting node UNION ALL SELECT tc.a, e.b, tc.distance + 1, tc.path_string || e.b || '.' AS path_string, tc.direct_connection FROM edges2 AS e JOIN transitive_closure AS tc ON e.a = tc.b WHERE tc.path_string NOT LIKE '%' || e.b || '.%' AND tc.distance < 3 ) SELECT * FROM transitive_closure --WHERE b=3 -- set the target node ORDER BY a,b,distance how to run this query in MySql? it will show error message like this: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RECURSIVE transitive_closure(a, b, distance, path_string) AS ( SELECT a, b, 1 A' at line 1

• Answer:

  The WITH RECURSIVE statement/method is applicable in PostgreSQL and Sybase (and maybe a few more, I think), so maybe you can look at this instead: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html It should show you some approaches using MySQL (and one or two in PHP, just to mention -- I know it's not in your tag list)