Sub Queries, Group By's, and Joins

Question

I am new to sql and am trying to complete an assignment for a class where were practicing using subqueries and joins. The question I'm struggling with is: Provide a list of the airport city names and the travelers (last name) who have traveled to each airport via a flight. Here are the tables in the database:Database Tables

Here is what I have so far:

SELECT Airport.CityName
FROM AIRPORT
GROUP BY AirportID
INNER JOIN FLIGHT
ON FLIGHT.AirportID = AIRPORT.AirportID
INNER JOIN TRAVELER
ON TRAVELER.TravelerID = FLIGHT.TravelerID
SELECT TravLastName
FROM TRAVELER

but I'm getting an error on the first "Inner" and I know I'm probably nowhere close to being right. Any help would be appreciated.

Answer 1

The joins look fine but there should only be one select at the beginning and the group by should come at the end

SELECT 
  Airport.CityName,
  TRAVELER.TravLastName
FROM AIRPORT
INNER JOIN FLIGHT
  ON FLIGHT.AirportID = AIRPORT.AirportID
INNER JOIN TRAVELER
  ON TRAVELER.TravelerID = FLIGHT.TravelerID
GROUP BY 
  Airport.CityName,
  TRAVELER.TravLastName;

Or, as we are not using any aggregate functions we can use DISTINCT. It is simpler and can run quicker.

SELECT DISTINCT
  Airport.CityName,
  TRAVELER.TravLastName
FROM AIRPORT
INNER JOIN FLIGHT
  ON FLIGHT.AirportID = AIRPORT.AirportID
INNER JOIN TRAVELER
  ON TRAVELER.TravelerID = FLIGHT.TravelerID;

Answer 2

You don't want GROUP BY, you want DISTINCT:

SELECT DISTINCT
  AIRPORT.CityName,
  TRAVELER.TravLastName
FROM AIRPORT
JOIN FLIGHT ON FLIGHT.AirportID = AIRPORT.AirportID
JOIN TRAVELER ON TRAVELER.TravelerID = FLIGHT.TravelerID

Some tidy ups:

• INNER is the default join type, so you can leave it out
• DISTINCT means remove duplicates
• FROM starts the list of tables to be joined. You can't add tables to the query in other places

A further tidy up would be to use table aliases, which rename the table in the context of the query - often using just the first letter of the table, to make the query smaller overall:

SELECT DISTINCT
  a.CityName,
  t.TravLastName
FROM AIRPORT a
JOIN FLIGHT f ON f.AirportID = a.AirportID
JOIN TRAVELER t ON t.TravelerID = f.TravelerID

The keyword AS may optionally be put between a table and its alias, eg FROM AIRPORT AS a.