SQL

Question

+-------------+-------------+-------------+------+
| AirlineCode | DepCityCode | DesCityCode | Cost |
+-------------+-------------+-------------+------+
| AC | hou | wis | 345 |
| am | hou | wis | 98 |
| am | mia | hou | 666 |
| am | wis | chi | 200 |
+-------------+-------------+-------------+------+

I have this table, I prompt the user for a depCity, DestCity and connections
The user is only able to do 0 and 1 connections. How can I figure it out If the user enter hou to wis 1 connection
to print hou to wis layover wis chi?

Answer 1

Tibor Santa How did you come up with that, I'm in awe 😵😁
I haven't even understand what was the question. 😁

Answer 2

Nice job. 👌

Answer 3

SELECT AirlineCode, DepCityCode, NULL as TransferAirlineCode, NULL as TransferCityCode, DesCityCode, Cost
FROM Flights
WHERE DepCityCode = user_dep
AND DesCityCode = user_des
AND user_conn = 0
UNION
SELECT f1.AirlineCode, f1.DepCityCode, f2.AirlineCode as TransferAirlineCode, f1.DesCityCode as TransferCityCode, f2. DesCityCode, f1.Cost + f2.Cost as Cost
FROM Flights f1
JOIN Flights f2
ON f1.DesCityCode = f2.DepCityCode
WHERE f1.DepCityCode = user_dep
AND f2.DesCityCode = user_des
AND user_conn = 1

Answer 4

dρlυѕρlυѕ I work with databases :)
The query will return single trips from the table if user chooses no connection, and connected trips via a transfer station, if 1 connection is allowed, by joining the table to itself.