I am working on a group project for the US Forest Service to create a dashboard on the US's arial firefighting operations and resources. This part is focused on creating a single data table for the third of the dashboard dedicated to data on the runways at the airports from which firefighting aircraft and can off when going to fight a fire and on which they may land afterwards.
The basic task is to join the three tables airports
, airport_schedules
, and runway
while limiting the data stored in the table to only that on airports which are actively used for arial firefighting in the US. I already have a character string of Airport_IDs I scraped from the web elsewhere indicating which airports these are out of all airports on American soil. Because I have not used SQL in two years, as a sanity check, I did this task two different ways, in MySQL Workbench, which I think are equivalent except that they are in different orders. Method 1, Step 1: select only the rows based on those previously ascertrained Airport_IDs (I limited the airport IDs to the first 4 only so as not to waste space) and the fields/columns I need.
-- create the dash_runs table (without runways counts)
SELECT Dep_Airport_ID, Runway_ID, `Length`, Width, Surface_Type_Condition
FROM `runways`.`runway`
WHERE Dep_Airport_ID IN ('09J','14A','18A','1V6');
-- create the dash_airports table
SELECT Dep_Airport_ID, ARP_Latitude, ARP_Longitude, Facility_Type, Wind_Indicator FROM airports
WHERE Dep_Airport_ID IN ('09J','14A','18A','1V6');
-- create the dash_air_scheds table
SELECT Dep_Airport_ID, Schedules FROM airport_schedules
WHERE Dep_Airport_ID IN ('09J','14A','18A','1V6');
Method 1, Step 2: run all three possible inner joins of the previous 3 queries
-- inner join of dash_air_scheds on dash_airports as 'dasda'
SELECT dash_air_scheds.Dep_Airport_ID, dash_airports.ARP_Latitude, dash_airports.ARP_Longitude, dash_air_scheds.Schedules, dash_airports.Facility_Type, dash_airports.Wind_Indicator
FROM dash_air_scheds
INNER JOIN dash_airports ON dash_air_scheds.Dep_Airport_ID = dash_airports.Dep_Airport_ID;
-- inner join of dash_airports on dash_runs as
SELECT dash_airports.Dep_Airport_ID, dash_airports.ARP_Latitude, dash_airports.ARP_Longitude, dash_airports.Facility_Type, dash_airports.Wind_Indicator, dash_runs.Runway_ID, dash_runs.Num_of_Runways, dash_runs.Length, dash_runs.Width, dash_runs.Surface_Type_Condition
FROM dash_airports
INNER JOIN dash_runs ON dash_airports.Dep_Airport_ID = dash_runs.Dep_Airport_ID;
-- inner join of dash_air_scheds on dash_runs as 'dasdr'
SELECT dash_air_scheds.Dep_Airport_ID, dash_air_scheds.Schedules, dash_runs.Runway_ID, dash_runs.Num_of_Runways, dash_runs.Length, dash_runs.Width, dash_runs.Surface_Type_Condition
FROM dash_air_scheds
INNER JOIN dash_runs ON dash_air_scheds.Dep_Airport_ID = dash_runs.Dep_Airport_ID;
IMPORTANT: Save the output tables of each of these queries!
Method 1, Step 3: run either possible version of the final inner join
-- Runway table query, version 1
SELECT dasda.Dep_Airport_ID, dasda.ARP_Latitude, dasda.ARP_Longitude, dasda.Schedules, dasda.Facility_Type, dasda.Wind_Indicator, drs.Num_of_Runways, drs.Length, drs.Width, drs.Surface_Type_Condition
FROM dash_air_scheds_on_dash_airports AS dasda
INNER JOIN dash_runways AS drs ON dasda.Dep_Airport_ID = drs.Dep_Airport_ID;
2330 row(s) returned 0.000 sec / 0.015 sec
-- Runway table query, version 2
SELECT dasdr.Dep_Airport_ID, dasdr.Schedules, dash_airports.ARP_Latitude, dash_airports.ARP_Longitude, dash_airports.Facility_Type, dash_airports.Wind_Indicator, dasdr.Runway_ID, dasdr.Num_of_Runways,dasdr.Length, dasdr.Width, dasdr.Surface_Type_Condition
FROM dash_air_scheds_on_dash_runs AS dasdr
INNER JOIN dash_airports ON dasdr.Dep_Airport_ID = dash_airports.Dep_Airport_ID;
2330 row(s) returned 0.000 sec / 0.016 sec
Both of these queries result in the same table with 2330 records. Now, on the other hand, if I do the same thing except just implement the WHERE clause at the end rather than at the beginning, I do not get the same output table.
Method 2, Step 1: an inner join of airport_schedules on airports
SELECT airport_schedules.Dep_Airport_ID, airport_schedules.Schedules, airports.ARP_Latitude, airports.ARP_Longitude, airports.Facility_Type, airports.Wind_Indicator
FROM airport_schedules
INNER JOIN airports ON airport_schedules.Dep_Airport_ID = airports.Dep_Airport_ID;
Method 2, Step 2:
-- Runway table query, version 3
SELECT sch.Dep_Airport_ID, sch.Schedules, sch.ARP_Latitude, sch.ARP.Longitude, sch.Facility_Type, sch.Wind_Indicator, run.Runway_ID, run.Num_of_Runways, run.Length, run.Width, run.Surface_Type_Condition
FROM airport_schedules_on_airports AS sch
INNER JOIN runway AS run ON sch.Dep_Airport_ID = run.Dep_Airport_ID
WHERE sch.Dep_Airport_ID IN ('09J','14A','18A','1V6');
1453 row(s) returned 0.015 sec / 0.203 sec
This is one of my first times asking about SQL queries, not exactly sure how to share the tables returned yet.
runways
.runway
``, but in method 2, step 2 the table isrunway
. Do these refer to the same table? $\endgroup$