CODEWITHSUNDEEP
mysql
mysterious
mysterious

Reputation: 1478

mysql query - 2 foreign keys

I have these two tables

airports: airportid, airportname
flights: flightid, from_airport, to_airport

where from_airport and to_airport are the foreign keys.

I can join tables either on airportid and from_airport or airportid and to_airport and either I get name of to_airport or name of from_airport but I want to select both to_airport and from_airport names either in one query or at minimum cost.

Is it possible?? How??

Here is my query:

SELECT
flight.idflight,
flight.idairline,
flight.from_airport,
flight.to_airport,
flight.number,
airports.name AS origin
FROM
flight
Inner Join airports ON flight.from_airport = airports.idairports

Upvotes: 2

Views: 237

Answers (1)

Maxime Pacary
Maxime Pacary

Reputation: 23091

Alias your tables when doing the joins:

SELECT
  flight.idflight,
  flight.idairline,
  flight.from_airport,
  flight.to_airport,
  flight.number,
  airport_from.name AS origin
  airport_to.name AS destination
FROM flight
  INNER JOIN airports airport_from ON flight.from_airport = airport_from.idairports
  INNER JOIN airports airport_to ON flight.to_airport = airport_to.idairports

Upvotes: 2

Related Questions