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
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.