SQL - return the cost of the cheapest flight with 1 en-route stop?

Question

I have this table:

The table

and I was trying to solve this problem given like this:

Give a single SQL query that returns the cheapest cost of flying between each pair of cities assuming we are willing to stop up to two times en route. For example, by stopping once (in Denver), we can get from SF to NY for 700 instead of 750. In this example, we could stop twice (in Denver and Chicago), but that would be more expensive (300 + 250 + 250 = 800)."

In this handout for question number 4b.

I just want it to display for only one en-route stop.

I have so far this query:

SELECT f1.fromCity, f2.toCity as destination, f1.toCity as scale, (MIN(f1.fare) + MIN(f2.fare)) as price
FROM flightfares f1, flightfares f2
WHERE f2.fromCity = f1.toCity
AND f1.fromCity != f2.toCity
GROUP BY f1.fromCity, f1.toCity, f2.toCity
HAVING MIN(f1.fare) AND MIN(f2.fare);

it displays the data from one city to another with 1 stop and the total price.

I am not sure what to do from there on to solve the problem. Can someone provide me with ideas or the solution?

EDIT: Join version?

SELECT f1.fromCity, f2.toCity as destination, f1.toCity as scale, (min(f1.fare) + min(f2.fare)) as price 
FROM flighfares f1
JOIN flighfares f2 ON(f2.fromCity = f1.toCity AND f1.fromCity != f2.toCity)
GROUP BY f1.fromCity, f1.toCity, f2.toCity
HAVING min(f1.fare) AND min(f2.fare);

Just to note: you're using the old join syntax which is not used any more, have a read here for the correct syntax https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax — Rich Benner, Oct 23 '17 at 08:23
I think a procedural approach would make much more sense for this. — Gurwinder Singh, Oct 23 '17 at 08:24
@FoxCy I am using MySQL for this problem. I am trying to learn SQL by myself — vtCode, Oct 23 '17 at 08:29
You have to start with a `JOIN` rather than a `WHERE`. Then, I think that the completeness of the query justifies a stored procedure rather than a simple request as @RichBenner and @GurwinderSingh suggests. — F0XS, Oct 23 '17 at 08:43
@Harshil For one is enough. Just want to know how to do this. Of course, 2 would be appreciated — vtCode, Oct 23 '17 at 08:47
@Gurwinder Singh Sorry, but what does it mean when you say "procedural approach"? — vtCode, Oct 23 '17 at 09:16
@vtCode - By using regular programmatic (Java, PLSQL, C#, ... whatever you have) approach rather than SQL. — Gurwinder Singh, Oct 23 '17 at 09:17

score 0 · Answer 1 · answered Oct 23 '17 at 10:04

This query will solve only for one stop as per your given data. This answer is based only for the task (4b) which is attached in question. Hope this will help..

select * from 
(select distinct start,dest,cost from pet 
union 
select distinct b.start,b.dest,MIN(total) cost 
from 
(select distinct a.start,(case when a.dest2 is null then dest1 else dest2 end)dest,(case when a.cost2 is NULL then a.cost1 else a.cost2+a.cost1 end) total 
from (select t1.start,t1.dest dest1,t2.dest dest2,t1.cost cost1,t2.cost cost2 
from pet t1 left join pet t2 on (t1.dest=t2.start and t1.start <> t2.dest))a)b group by b.start,b.dest order by cost)c 
group by start,dest;

The answer:

+---------+---------+------+
| start   | dest    | cost |
+---------+---------+------+
| Chicago | NY      |  250 |
| Denver  | Chicago |  250 |
| Denver  | NY      |  400 |
| Denver  | SF      |  250 |
| SF      | Chicago |  550 |
| SF      | Denver  |  300 |
| SF      | NY      |  700 |
+---------+---------+------+

For more stops this method cannot help you more. Again saying this answer is only for the question you asked.

score 0 · Answer 2 · answered Oct 23 '17 at 10:26

Maybe this:

SELECT First_airport, Final_airport, min(fare)
FROM (
SELECT
f1._from AS First_airport,
f1._to AS Mid_airport1,
f2._to AS Mid_airport2,
coalesce(f3._to, f2._to, f1._to) as Final_airport,
f1.cost + coalesce( f2.cost, 0) +coalesce( f3.cost, 0)   as fare
FROM
flights f1 LEFT JOIN flights f2 ON
f1._to = f2._from LEFT JOIN  flights f3 ON
f2._to = f3._from
UNION ALL SELECT
_from, '', '', _to, cost FROM flights
) t
GROUP BY First_airport, Final_airport

Adjust your column names.

SQL - return the cost of the cheapest flight with 1 en-route stop?

2 Answers2