mysql查询 - 2个外键

Question

我有这两张表

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

其中 from_airport 和 to_airport 是外键。

我可以在 airportid 和 from_airport 或 airportid 和 to_airport 上加入表，或者我得到名称to_airport 或 from_airport 的名称，但我想在一个查询中或至少同时选择 to_airport 和 from_airport 名称成本。

这可能吗？怎么办？？

这是我的查询:

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

Answer 1

在进行连接时为您的表添加别名:

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