365 Days of SQL: Day 151

I read the puzzle for today and I was thinking to myself, “Oh my gosh!” This looks really tough. However, as I began solving the question, I realised that the solution was rather smooth. It did not come easy but it helped to play around a bit. Yay for that! 🙂

Puzzle #36: Traveling Salesman

I tried the solution in the live oracle environment here.

WITH temp1 AS (
SELECT 'Austin' AS DepartureCity, 'Dallas' AS ArrivalCity, 100 AS Cost FROM DUAL UNION ALL
SELECT 'Dallas' AS DepartureCity, 'Memphis' AS ArrivalCity, 200 AS Cost FROM DUAL UNION ALL
SELECT 'Memphis' AS DepartureCity,'Des Moines' AS ArrivalCity, 300 AS Cost FROM DUAL UNION ALL
SELECT 'Dallas' AS DepartureCity, 'Des Moines' AS ArrivalCity, 400 AS Cost FROM DUAL
)
SELECT a.DepartureCity, a.ArrivalCity,b.DepartureCity, b.ArrivalCity, c.DepartureCity, c.ArrivalCity,
COALESCE(a.Cost + b.Cost+ c.Cost,a.Cost + b.Cost, a.Cost) AS TotalCost FROM temp1 a
LEFT JOIN temp1 b
ON a.ArrivalCity = b.DepartureCity
LEFT JOIN temp1 c
ON b.ArrivalCity = c.DepartureCity
WHERE a.DepartureCity = 'Austin';