rcte.sql

CREATE DATABASE IF NOT EXISTS rcte_demo;

USE rcte_demo;

CREATE TABLE IF NOT EXISTS stations_route (
    station_from VARCHAR(22),
    station_to   VARCHAR(22),
    distance     NUMERIC(4,2)
);

INSERT INTO stations_route VALUES
('MORDEN','SOUTH WIMBLEDON',1.46),
('SOUTH WIMBLEDON','COLLIERS WOOD',1.14),
('COLLIERS WOOD','TOOTING BROADWAY',1.21),
('TOOTING BROADWAY','TOOTING BEC',1.11),
('TOOTING BEC','BALHAM',0.98),
('BALHAM','CLAPHAM SOUTH',1.16),
('CLAPHAM SOUTH','CLAPHAM COMMON',1.26),
('CLAPHAM COMMON','CLAPHAM NORTH',0.63),
('CLAPHAM NORTH','STOCKWELL',0.82),
('STOCKWELL','OVAL',1.37),
('OVAL','KENNINGTON',0.82),
('KENNINGTON','ELEPHANT & CASTLE',0.9),
('ELEPHANT & CASTLE','BOROUGH',0.85),
('BOROUGH','LONDON BRIDGE',0.68),
('LONDON BRIDGE','BANK',0.69),
('BANK','MOORGATE',0.82),
('MOORGATE','OLD STREET',0.69),
('OLD STREET','ANGEL',1.45),
('ANGEL','KINGS CROSS',1.38),
('KINGS CROSS','EUSTON',0.61),
('EUSTON','CAMDEN TOWN',1.72),
('KENNINGTON','WATERLOO',1.9),
('WATERLOO','EMBANKMENT',0.71),
('EMBANKMENT','CHARING CROSS',0.27),
('CHARING CROSS','LEICESTER SQUARE',0.47),
('LEICESTER SQUARE','TOTTENHAM COURT ROAD',0.4),
('TOTTENHAM COURT ROAD','GOODGE STREET',0.63),
('GOODGE STREET','WARREN STREET',0.47),
('WARREN STREET','EUSTON',0.58),
('EUSTON','MORNINGTON CRESCENT',0.82),
('MORNINGTON CRESCENT','CAMDEN TOWN',0.64),
('CAMDEN TOWN','KENTISH TOWN',1.15),
('KENTISH TOWN','TUFNELL PARK',0.8),
('TUFNELL PARK','ARCHWAY',0.89),
('ARCHWAY','HIGHGATE',1.71),
('HIGHGATE','EAST FINCHLEY',1.64),
('EAST FINCHLEY','FINCHLEY CENTRAL',2.43),
('FINCHLEY CENTRAL','MILL HILL EAST',1.52),
('FINCHLEY CENTRAL','WEST FINCHLEY',1.14),
('WEST FINCHLEY','WOODSIDE PARK',0.98),
('WOODSIDE PARK','TOTTERIDGE & WHETSTONE',1.51),
('TOTTERIDGE & WHETSTONE','HIGH BARNET',2.48),
('CAMDEN TOWN','CHALK FARM',0.85),
('CHALK FARM','BELSIZE PARK',1.11),
('BELSIZE PARK','HAMPSTEAD',1.21),
('HAMPSTEAD','GOLDERS GREEN',2.35),
('GOLDERS GREEN','BRENT CROSS',1.56),
('BRENT CROSS','HENDON CENTRAL',1.11),
('HENDON CENTRAL','COLINDALE',2.11),
('COLINDALE','BURNT OAK',1.32),
('BURNT OAK','EDGWARE',1.47);

WITH RECURSIVE possible_route AS (
    SELECT sr.station_to,
           CONCAT (sr.station_from, '->', sr.station_to) AS route,
           sr.distance
    FROM stations_route sr
    WHERE sr.station_from = 'MORDEN'
UNION ALL
    SELECT sr.station_to,
           CONCAT (pr.route, '->', sr.station_to) AS route,
           pr.distance + sr.distance
    FROM possible_route pr
INNER JOIN stations_route sr
    ON sr.station_from = pr.station_to
)
SELECT pr.route,
       pr.distance
FROM possible_route pr
WHERE pr.station_to = 'EUSTON'
ORDER BY pr.distance;