Size: a a a
EB
ST
EB
VI
ST
SELECT CONCAT_WS(' - ', fl.from_city, fl.to_city, fl2.to_city, fl3.to_city) AS 'Route',
(fl.duration + fl2.duration + IFNULL(fl3.duration, 0)) AS 'Duration'
FROM `flights` AS fl
LEFT JOIN flights AS fl2 ON fl.to_city = fl2.from_city
LEFT JOIN flights AS fl3 ON fl2.to_city = fl3.from_city
WHERE (fl.from_city = "Kharkiv"
AND fl.to_city = "Vancouver")
OR (fl.from_city = "Kharkiv"
AND fl2.to_city = "Vancouver")
OR (fl.from_city = "Kharkiv"
AND fl3.to_city = "Vancouver")ST
P
with recursive build_path as
( select flights.from,flights.to,concat(flights.from,',',flights.to) as current_path, 0 as cross_point,duration from flights where 1
union all
select flights.from as `from`, build_path.to as `to`,concat(flights.from,',',build_path.current_path)
as current_path,1+build_path.cross_point as cross_point ,flights.duration+build_path.duration as duration from flights, build_path
where flights.to =build_path.from and `cross_point`<=4 and not find_in_set(flights.from,build_path.current_path) )
select * from build_path where `from`='kh' and `to`='van' order by duration limit 1;
VI
VI
ST
EB
with recursive build_path as
( select flights.from,flights.to,concat(flights.from,',',flights.to) as current_path, 0 as cross_point,duration from flights where 1
union all
select flights.from as `from`, build_path.to as `to`,concat(flights.from,',',build_path.current_path)
as current_path,1+build_path.cross_point as cross_point ,flights.duration+build_path.duration as duration from flights, build_path
where flights.to =build_path.from and `cross_point`<=4 and not find_in_set(flights.from,build_path.current_path) )
select * from build_path where `from`='kh' and `to`='van' order by duration limit 1;
ST
ST
VI
ST
DK
SELECT
CONCAT(f1.`from_city`, '-', IF(f1.`from_city` = f2.`from_city`, '', CONCAT(f1.`to_city`, '-')), f2.`to_city`) AS Route,
IF(f1.`from_city` = f2.`from_city`, f1.`duration`, f1.`duration` + f2.`duration`) AS Duration
FROM `flights` f1
LEFT JOIN `flights` f2 ON (f1.`to_city` = f2.`from_city` OR f1.`from_city` = f2.`from_city`)
WHERE f1.`from_city` = 'Kharkiv' AND f2.`to_city` = 'Kyiv'
ORDER BY Duration, Route
LIMIT 1
ST
SELECT CONCAT_WS(' - ', fl.from_city, fl.to_city, fl2.to_city, fl3.to_city) AS 'Route',
(fl.duration + fl2.duration + IFNULL(fl3.duration, 0)) AS 'Duration'
FROM `flights` AS fl
LEFT JOIN flights AS fl2 ON fl.to_city = fl2.from_city
LEFT JOIN flights AS fl3 ON fl2.to_city = fl3.from_city
WHERE (fl.from_city = "Kharkiv"
AND fl.to_city = "Vancouver")
OR (fl.from_city = "Kharkiv"
AND fl2.to_city = "Vancouver")
OR (fl.from_city = "Kharkiv"
AND fl3.to_city = "Vancouver")
ORDER BY Duration, Route
LIMIT 1ST
SELECT
CONCAT(f1.`from_city`, '-', IF(f1.`from_city` = f2.`from_city`, '', CONCAT(f1.`to_city`, '-')), f2.`to_city`) AS Route,
IF(f1.`from_city` = f2.`from_city`, f1.`duration`, f1.`duration` + f2.`duration`) AS Duration
FROM `flights` f1
LEFT JOIN `flights` f2 ON (f1.`to_city` = f2.`from_city` OR f1.`from_city` = f2.`from_city`)
WHERE f1.`from_city` = 'Kharkiv' AND f2.`to_city` = 'Kyiv'
ORDER BY Duration, Route
LIMIT 1
DC