如何根据一个表中的两个机场代码选择两个机场名称

Question

我有这个模式：

CREATE TABLE Airport (
  airportIATA CHAR(3) NOT NULL,
  name VARCHAR(45) NOT NULL, 
  PRIMARY KEY (airportIATA) 
  );
CREATE TABLE Flight (
  flightNumber VARCHAR(6) NOT NULL,
  timeOfDeparture TIME NOT NULL,
  departureAirport_IATA CHAR(3) NOT NULL REFERENCES Airport(airportIATA),
  stopOverAirport_IATA CHAR(3) REFERENCES Airport(airportIATA),
  arrivalAirport_IATA CHAR(3) NOT NULL REFERENCES Airport(airportIATA),
  PRIMARY KEY (flightNumber, departureAirport_IATA, arrivalAirport_IATA)
  );

有了这些记录：

INSERT INTO Airport VALUES ('HEL','Helsinki/Vantaa Airport');
INSERT INTO Airport VALUES ('STO','Stockholm Airport');
INSERT INTO Airport VALUES ('TAL', 'Lennart Meri Tallinn Airport');
INSERT INTO Flight VALUES ('AY101', '13:30', 'HEL', NULL, 'STO');
INSERT INTO Flight VALUES ('AA202', '16:15', 'STO', 'TAL', 'HEL');

到目前为止，我有这些疑问：

SELECT flightNumber, timeOfDeparture, departureAirport_IATA, arrivalAirport_IATA
FROM Flight
WHERE stopOverAirport_IATA IS NULL;

SELECT flightNumber, timeOfDeparture, name AS 'Arrival Airport'
FROM Flight, Airport
WHERE Flight.stopOverAirport_IATA IS NULL AND Flight.arrivalAirport_IATA = Airport.airportIATA;

我的问题是，我如何更改后一个查询，使其包含到达和出发机场的名称（正如我们在第一个查询中获得的IATA代码一样）？

Answer 1

尝试这样的事情：

SELECT
  flightNumber, 
  timeOfDeparture, 
  DA.name AS 'Departure Airport',
  AA.name AS 'Arrival Airport'
FROM
  Flight As F
  INNER JOIN Airport As DA
  ON F.departureAirport_IATA = DA.airportIATA
  INNER JOIN Airport As AA
  ON F.arrivalAirport_IATA = AA.airportIATA
WHERE 
  F.stopOverAirport_IATA IS NULL
;