SQL intersection - error in syntax

debugcn 投稿 Dev

Sick0ne

I am trying search for flights departing from London and Arriving in Paris. The information about the cities are located in the Airport table, while the abbreviations for the departing and arriving airports are located in Flight.

Here's my code for the problem:

SELECT flightNo,flightCompany
FROM Flight
JOIN Airport
ON Airport.airportId=Flight.depAirport
WHERE Airport.city='London'

INTERSECT

SELECT flightNo,flightCompany
FROM Flight
JOIN Airport
ON Airport.airportId=Flight.arrAirport
WHERE Airport.city='Paris';

It returns 3 errors -

SQL Status: HY000 Error code: 1000 Syntax error in SQL statement
SQL Status: HY000 Error code: 1000
SQL Status: HY000 Error code: 1000 syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

Gordon Linoff

How about re-writing the query using JOINs instead?

SELECT f.flightNo, f.flightCompany
FROM Flight f JOIN
     Airport ad
     ON ad.airportId = f.depAirport AND ad.city = 'London' JOIN
     Airport aa
     ON aa.airportId = f.arrAirport AND aa.city = 'Paris';

Or, using EXISTS?

SELECT f.*
FROM Flight f
WHERE EXISTS (SELECT 1
              FROM Airport ad
              WHERE ad.airportId = f.depAirport AND ad.city = 'London'
             ) AND
      EXISTS (SELECT 1
              FROM Airport aa
              WHERE aa.airportId = f.arrAirport AND aa.city = 'Paris'
             );

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]