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
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
Sorry that the formatting was not maintained in the reply above.
From an example in the documentation above, I've added n, which represents the level within the hierarchy: