Two tables used for recursion using recursive common table expressions

flights.sql

-- source: https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/sqlp/rbafyrecursivequeries.htm
-- Example: Two tables used for recursion using recursive common table expressions

DROP TABLE IF EXISTS FLIGHTS;
CREATE TABLE FLIGHTS (DEPARTURE CHAR(20),
                      ARRIVAL CHAR(20),
                      CARRIER CHAR(15),
                      FLIGHT_NUMBER CHAR(5),
                      PRICE INT);


INSERT INTO FLIGHTS VALUES('New York', 'Paris', 'Atlantic', '234', 400);
INSERT INTO FLIGHTS VALUES('Chicago', 'Miami', 'NA Air', '2334', 300);
INSERT INTO FLIGHTS VALUES('New York', 'London', 'Atlantic', '5473', 350);
INSERT INTO FLIGHTS VALUES('London', 'Athens'  , 'Mediterranean', '247', 340);
INSERT INTO FLIGHTS VALUES('Athens', 'Nicosia' , 'Mediterranean', '2356', 280);
INSERT INTO FLIGHTS VALUES('Paris', 'Madrid' , 'Euro Air',  '3256', 380);
INSERT INTO FLIGHTS VALUES('Paris', 'Cairo' , 'Euro Air', '63', 480);
INSERT INTO FLIGHTS VALUES('Chicago', 'Frankfurt', 'Atlantic', '37', 480);
INSERT INTO FLIGHTS VALUES('Frankfurt', 'Moscow', 'Asia Air', '2337', 580);
INSERT INTO FLIGHTS VALUES('Frankfurt', 'Beijing', 'Asia Air',  '77', 480);
INSERT INTO FLIGHTS VALUES('Moscow', 'Tokyo', 'Asia Air', '437', 680);
INSERT INTO FLIGHTS VALUES('Frankfurt', 'Vienna', 'Euro Air', '59', 200);
INSERT INTO FLIGHTS VALUES('Paris', 'Rome', 'Euro Air', '534', 340);
INSERT INTO FLIGHTS VALUES('Miami', 'Lima', 'SA Air', '5234', 530);
INSERT INTO FLIGHTS VALUES('New York', 'Los Angeles', 'NA Air', '84', 330);
INSERT INTO FLIGHTS VALUES('Los Angeles', 'Tokyo', 'Pacific Air', '824', 530);
INSERT INTO FLIGHTS VALUES('Tokyo', 'Hawaii', 'Asia Air', '94', 330);
INSERT INTO FLIGHTS VALUES('Washington', 'Toronto', 'NA Air', '104', 250);

DROP TABLE IF EXISTS TRAINS;
CREATE TABLE TRAINS(DEPARTURE CHAR(20),
                    ARRIVAL CHAR(20),
                    RAILLINE CHAR(15),
                    TRAIN CHAR(5),
                    PRICE INT);

INSERT INTO TRAINS VALUES('Chicago', 'Washington', 'UsTrack', '323', 90);
INSERT INTO TRAINS VALUES('Madrid', 'Barcelona', 'EuroTrack', '5234', 60);
INSERT INTO TRAINS VALUES('Washington' , 'Boston' , 'UsTrack', '232', 50);

DROP TABLE IF EXISTS FLIGHTSTATS;
CREATE TABLE FLIGHTSTATS(FLIGHT CHAR(5),
                         ON_TIME_PERCENT DECIMAL(5,2),
                         CANCEL_PERCENT DECIMAL(5,2));

INSERT INTO FLIGHTSTATS VALUES('234', 85.0, 0.20);
INSERT INTO FLIGHTSTATS VALUES('2334',  92.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('5473', 86.2, 0.10);
INSERT INTO FLIGHTSTATS VALUES('247',  91.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('2356', 91.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('3256', 92.0 , 0.10);
INSERT INTO FLIGHTSTATS VALUES('63', 90.5 , 0.10);
INSERT INTO FLIGHTSTATS VALUES('37', 87.0 , 0.20);
INSERT INTO FLIGHTSTATS VALUES('2337', 80.0, 0.20);
INSERT INTO FLIGHTSTATS VALUES('77', 86.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('437', 81.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('59',  85.0, 01.0);
INSERT INTO FLIGHTSTATS VALUES('534', 87.0 , 01.0);
INSERT INTO FLIGHTSTATS VALUES('5234', 88.0, 0.20);
INSERT INTO FLIGHTSTATS VALUES('84', 88.0, 0.1);
INSERT INTO FLIGHTSTATS VALUES('824', 93.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('94', 92.0, 0.10);
INSERT INTO FLIGHTSTATS VALUES('104', 93.0, 0.10);


WITH RECURSIVE destinations (departure, arrival, connections, flights, trains, cost) AS
  (SELECT f.departure, f.arrival, 0, 1, 0, price
          FROM  flights f
          WHERE f.departure = 'Chicago'
   UNION ALL
   SELECT t.departure, t.arrival, 0, 0, 1, price
          FROM trains t
          WHERE t.departure = 'Chicago'
   UNION ALL
   (SELECT r.departure, b.arrival, r.connections + 1 , r.flights + 1, r.trains,
             r.cost + b.price
          FROM  destinations r, flights b
          WHERE r.arrival = b.departure
   UNION ALL
   SELECT r.departure, c.arrival, r.connections + 1 ,
             r.flights, r.trains + 1, r.cost + c.price
          FROM destinations r, trains c
          WHERE r.arrival = c.departure))
SELECT departure, arrival, connections, flights, trains, cost
       FROM destinations;

--       departure       |       arrival        | connections | flights | trains | cost
-- ----------------------+----------------------+-------------+---------+--------+------
--  Chicago              | Miami                |           0 |       1 |      0 |  300
--  Chicago              | Frankfurt            |           0 |       1 |      0 |  480
--  Chicago              | Washington           |           0 |       0 |      1 |   90
--  Chicago              | Moscow               |           1 |       2 |      0 | 1060
--  Chicago              | Beijing              |           1 |       2 |      0 |  960
--  Chicago              | Vienna               |           1 |       2 |      0 |  680
--  Chicago              | Lima                 |           1 |       2 |      0 |  830
--  Chicago              | Toronto              |           1 |       1 |      1 |  340
--  Chicago              | Boston               |           1 |       0 |      2 |  140
--  Chicago              | Tokyo                |           2 |       3 |      0 | 1740
--  Chicago              | Hawaii               |           3 |       4 |      0 | 2070
-- (11 rows)