how to stop INNER JOIN from showing repeat data

Question

I am working with data on multiple tables and I am trying to get some info but I keep getting repeat data.

  SELECT Airport.city, StateInfo.state_name, TravelInfo.destination, 
  Carrier.unique_carrier_name, CarrierInfo.passengers
  FROM Airport
  INNER JOIN TravelInfo 
  ON Airport.airport_id = TravelInfo.destination_airport_id 
  INNER JOIN Flights
  ON Airport.airport_id = Flights.destination_airport_id
  INNER JOIN StateInfo 
  ON Airport.airport_id = StateInfo.airport_id 
  INNER JOIN Carrier
  ON Flights.airline_id = Carrier.airline_id
  INNER JOIN CarrierInfo
  ON Carrier.airline_id = CarrierInfo.airline_id
  WHERE Airport.state = 'CO';

What I am trying to do is get the city name the carrier and a number of passengers but it seems that the output keeps repeating the data. For example I will get:

As you can see the data gets repeated a few hundred times is there a way to fix this?

When you inner join tables using non-primaryKey columns, it is possible you will get duplicated queried data. For example: there may be multiple records in TravelInfo table with same desitination_airport_id, or there may be multiple records in Flights table with same destination_airport_id. If you could retrieve some sample data for each table based on "CarrierInfo.passengers=7713" and post it here, we may get a better idea what we are dealing with. — YK S, Oct 15 '17 at 21:24
Hi. Please clearly explain "getting repeat data", and why it is a problem here--why is *that* output not what you want--ie tell us what your query is suposed to do. You don't clearly say how "number of passengers" in a row relates to its "city name" & "the carrier ". Please read & act on [mcve]. [Please use text if you can. Like instead of that image.](https://stackoverflow.com/questions/14588304/composite-primary-key-vs-additional-id-column). — philipxy, Oct 17 '17 at 01:17

score 0 · Answer 1 · answered Oct 15 '17 at 20:46

You can sum up passangers for city and destination.

SELECT Airport.city, StateInfo.state_name, TravelInfo.destination, 
  Carrier.unique_carrier_name
  ,sum( CarrierInfo.passengers) as passengers
  FROM Airport
  INNER JOIN TravelInfo 
  ON Airport.airport_id = TravelInfo.destination_airport_id 
  INNER JOIN Flights
  ON Airport.airport_id = Flights.destination_airport_id
  INNER JOIN StateInfo 
  ON Airport.airport_id = StateInfo.airport_id 
  INNER JOIN Carrier
  ON Flights.airline_id = Carrier.airline_id
  INNER JOIN CarrierInfo
  ON Carrier.airline_id = CarrierInfo.airline_id
  WHERE Airport.state = 'CO'
  GROUP BY 1,2,3,4;

But I believe this would over report the passenger number because I see duplicate passangers in your output. So somewhere in your joins you are duplicating data.

how to stop INNER JOIN from showing repeat data

1 Answers1