Hi i need use this sql on mysql.

WITH destinations (departure, arrival, connections, cost) AS
    (SELECT a.departure, a.arrival, 0, price
            FROM flights a 
            WHERE a.departure = 'Chicago' OR
                  a.departure = 'New York'
     UNION ALL 
     SELECT r.departure, b.arrival, r.connections + 1,
                  r.cost + b.price 
            FROM destinations r, flights b 
            WHERE r.arrival = b.departure) 
SELECT departure, arrival, connections, cost 
       FROM destinations

HOW i can use this on mysql?
Thanks a lot.

Joseph

Recommended Answers

All 2 Replies

Member Avatar for spthorn
SELECT a.departure, a.arrival, 0 AS connections, price AS cost FROM flights AS a WHERE a.departure = 'Chicago' OR a.departure = 'New York'
UNION
SELECT r.departure, b.arrival, r.connections + 1, r.cost + b.price AS cost FROM destinations r, flights b WHERE r.arrival = b.departure
SELECT a.departure, a.arrival, 0 AS connections, price AS cost FROM flights AS a WHERE a.departure = 'Chicago' OR a.departure = 'New York'
UNION
SELECT r.departure, b.arrival, r.connections + 1, r.cost + b.price AS cost FROM destinations r, flights b WHERE r.arrival = b.departure

=}

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.