Reputation: 1478
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
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