2 months ago Jon Armstrong

Sorry that the formatting was not maintained in the reply above.

 
2 months ago Jon Armstrong

From an example in the documentation above, I've added n, which represents the level within the hierarchy:

CREATE TABLE bus_routes (origin varchar(50), dst varchar(50));
INSERT INTO bus_routes VALUES
 ('New York', 'Boston'),
 ('Boston', 'New York'),
 ('New York', 'Washington'),
 ('Washington', 'Boston'),
 ('Washington', 'Raleigh')
;

WITH RECURSIVE paths (n, cur_path, cur_dest) AS (
 SELECT 1 AS n, origin, origin
   FROM bus_routes
  WHERE origin='New York'
  UNION
 SELECT n + 1, CONCAT(paths.cur_path, ',', bus_routes.dst), bus_routes.dst
   FROM paths, bus_routes
  WHERE paths.cur_dest = bus_routes.origin
    AND LOCATE(bus_routes.dst, paths.cur_path) = 0
 )
SELECT * FROM paths;