mysql查询-2个外键 [英] mysql query - 2 foreign keys

问题描述

我有这两个表

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在一个查询中或以最低的费用命名.

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.

有可能吗?怎么样?

这是我的查询:

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

推荐答案

在进行联接时别名为表:

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

这篇关于mysql查询-2个外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！