1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| WITH RECURSIVE journey (TO_TOWN, STEPS, DISTANCE, WAY)
AS
(SELECT DISTINCT JNY_FROM_TOWN, 0, 0, CAST('PARIS' AS VARCHAR(MAX))
FROM u_recursive.T_JOURNEY
WHERE JNY_FROM_TOWN = 'PARIS'
UNION ALL
SELECT JNY_TO_TOWN, departure.STEPS + 1
, departure.DISTANCE + arrival.JNY_KM
, departure.WAY + ', ' + arrival.JNY_TO_TOWN
FROM u_recursive.T_JOURNEY AS arrival
INNER JOIN journey AS departure
ON departure.TO_TOWN = arrival.JNY_FROM_TOWN)
SELECT *
FROM journey
WHERE TO_TOWN = 'TOULOUSE' |