I have two tables. delayedFlights which has these following attributes:
- ID_of_Delayed_Flight
- Month
- DayofMonth
- DayOfWeek
- DepTime
- ScheduledDepTime
- ArrTime
- ScheduledArrTime
- UniqueCarrier
- FlightNum
- ActualFlightTime
- scheduledFlightTime
- AirTime
- ArrDelay
- DepDelay
- Orig
- Dest
- Distance
and airport which has these attributes:
- airportCode
- airportName
- City
- State
I am trying to write a query which Lists the top 5 distinct states in which a flight between different airports within the same state has been delayed (either in arrival or departure) by descending order with respect to number of such delays. I'm not really sure how to write this query as I'm not sure how to query both tables at the same time, could someone give me any pointers on how to do this?
This is what I've tried
SELECT state, COUNT(*) AS num_delays
FROM delayed_flights
WHERE state = state
GROUP BY state
ORDER BY num_delays DESC
LIMIT 5;
(But obviously does not work)
CodePudding user response：
You just need to understand joins you have to join to airport twice once for origin once for destination. we add aliases to make it easier to read and know the source tables of the selected fields. we can group by just origin or destination state since we're checking to make sure they are the same. Inner join requires an entry in both tables for a record to show. left/right (outer joins) show all records from one table and only those that match from the other whereas full outer shows all records from both tables even if no match is found. and cross join shows all records linked to all records in both tables.
This does assume that org and dest are related to the airportcode in the delayed flights table.
SELECT o.state, COUNT(*) AS num_delays
FROM delayed_flights df
INNER JOIN airport O
on o.airportCode = df.orig
INNER JOIN airport D
on d.airportCode = df.dest
WHERE O.state = D.state
GROUP BY o.state
ORDER BY num_delays DESC
LIMIT 5;