There are 3 tables: with flights of flights of flights, with names of the airports airport and with names of the cities city . You need to take one of the flights and find out the city of departure and the city of arrival.

in the flight table, the airports are marked fromAirport and toAirport these codes correspond to the airport.code . Each airport.code has its own airport.cityCode . Next on this cityCode you need to select the city city.code . Each city.code has its own city.name is what I need to take. Such is the triple nesting.

Tell me how to do it in one request?

UPD

I tried to use this query:

 SELECT * FROM flights, airport, city WHERE flights.fromAirport = airport.code AND flights.toAirport = airport.code ORDER BY flights.upload DESC LIMIT 1;

How to cram the third table here - I have no idea.

  • What have you tried to do? What exactly does not work? - Dmitriy Simushev
  • one
    This can be done using the JOIN binding of the desired tables. - Alex Krass
  • @DmitriySimushev, SELECT * FROM flights , airport , city WHERE flights . fromAirport = airport . code and flights . toAirport = airport . code ORDER BY flights . upload DESC LIMIT 1 I didn’t even work with a double, I have no idea how to cram the third table here - user193361
  • You need 2 copies of the tables of airports and cities - separately for departure and separately for arrival. - Akina

3 answers 3

 SELECT f.flightNumber, cF.name townFrom, cT.name townTo FROM flight f, airport aF, airport aT, city cF, city cT WHERE f.fromAirport = aF.code AND aF.cityCode = cF.code AND f.toAirport = aT.code AND aT.cityCode = cT.code -- AND f.flightNumber IN (123,456,789)
  • It looks like what I need, for some reason, by answering your request I get a warning. #1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay #1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay . The query itself has been slightly edited by SELECT * FROM flights f, airport aF, airport aT, city cF, city cT WHERE f.fromAirport = aF.code AND aF.cityCode = cF.code AND f.toAirport = aT.code AND aT.cityCode = cT.code - user193361
  • Well, they simply tell you that the options will be painfully dead. Add a filter to get data only for the necessary flights. - Akina
  • where do they come from there? ) there are only five records in the table with flights flights . Having tried different filters, the result is the same - user193361
  • And how many cities and airports? Well, at the same time - what is MAX_JOIN_SIZE in session ... - Akina
  • actually figured out everything is fine - user193361

Option using JOIN construction:

 SELECT f.code, cf.name AS fromCity, ct.name AS toCity FROM flight f INNER JOIN airport afrom ON f.fromAirport = afrom.code INNER JOIN airport ato ON f.toAirport = ato.code INNER JOIN city cf ON afrom.cityCode = cf.code INNER JOIN city ct ON ato.cityCode = ct.code ORDER BY f.code;

And here is a working example on SQLFiddle.

    You should have something like this:

     SELECT name FROM city JOIN airport ON city.code=airport.cityCode JOIN flights ON airport.code=flights.fromAirport WHERE flights.fromAirport=12345