2 months ago Jon Armstrong
This comment has the status of 'removed' and can only be seen by you.

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

 
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;