SQL intersection - error in syntax

Question

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

That is a client/front end tool, but which DBMS are you using? — , Feb 21 '16 at 22:42
I just have the tables imported into Libreoffice via an .odb file. It is not for any practical application - just looking for a solution to a question asked by our uni. — Sick0ne, Feb 21 '16 at 22:44
No idea. The code you have shown is valid SQL. So maybe whatever DBMS LibreOffice is using, it doesn't support that. — , Feb 21 '16 at 22:45
Is this supposed to get flights that depart from London and arrive in Paris or any flight departing from London and any flight arriving in Paris? — Brandon, Feb 21 '16 at 22:45
It gives 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 — Sick0ne, Feb 21 '16 at 22:46
it is supposed to be all flights that are both departing from London and arriving in Paris. — Sick0ne, Feb 21 '16 at 22:46
Is it possible to add a couple of example rows from each of the tables to the question? — tomp, Feb 21 '16 at 22:56
FWIW, when I run your query on the ANSI-compliant SQL DB that my company happens to use, I don't get any syntax errors, so it sounds like this issue could be a peculiarity with your particular back-end. — 500 - Internal Server Error, Feb 21 '16 at 22:59
It sounds like you can get the error returned by the database engine by running the command using the "Run the SQL command directly" option in the "Edit" menu. Can you try that? — tomp, Feb 21 '16 at 23:18
And do those tables actually exist (do you get results for `select * from flight` and for `select * from airport`)? — 500 - Internal Server Error, Feb 21 '16 at 23:26
Yes, for instance this query works: SELECT Flight.flightNo, Flight.flightCompany, Flight.depAirport, Flight.arrAirport FROM Flight, Airport WHERE Flight.depAirport = Airport.airportId AND Airport.city = 'London'; — Sick0ne, Feb 21 '16 at 23:28

Gordon Linoff · Accepted Answer · 2016-02-22T00:20:01.050

3

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'
             );

edited Feb 22 '16 at 00:20

answered Feb 21 '16 at 22:58

Gordon Linoff

1,242,037
58
646
786

I see how that might work, but for some reason it doesn't output any results (there is a combination of airports that should be shown). – Sick0ne Feb 21 '16 at 23:05
I am unsure if I should be changing anything in the second code, as I am somewhat new to SQL, but copying and running it as written gives an error - the data content could not be loaded, Table not found in statement. – Sick0ne Feb 21 '16 at 23:12
1

@Sick0ne . . . The second query had "airport". Perhaps your database is case sensitive about table names. – Gordon Linoff Feb 22 '16 at 00:20
1

Thank you for the answer. The second query works as intended. Decided to go with the following though: `SELECT F.flightNo,F.FlightCompany FROM Flight F, Airport A1, Airport A2 WHERE (F.depAirport=A1.airportId AND A1.city='London') AND (F.arrAirport=A2.airportId AND A2.city='Paris')` – Sick0ne Feb 23 '16 at 09:13
1

@Sick0ne . . . You should learn to use proper explicit `JOIN` syntax: never use commas in the `FROM` clause. – Gordon Linoff Feb 24 '16 at 02:09

Jim K · Answer 2 · 2016-02-22T23:44:18.423

1

Quoting rules and case sensitivity using the default LibreOffice Base / HSQLDB 1.8 setup are tricky. Here is what you need to do:

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';

Or better, modify Gordon's answer as follows:

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';

edited Feb 22 '16 at 23:44

answered Feb 22 '16 at 23:21

Jim K

12,824
2
22
51

See, this is what Libre Office does automatically for you. If you save a file, it's going to add the quotation marks by itself - if you open your query again, it's going to look that way. Decided to go with this in the end (and yes, Gordon's second code works): `SELECT F.flightNo,F.FlightCompany FROM Flight F, Airport A1, Airport A2 WHERE (F.depAirport=A1.airportId AND A1.city='London') AND (F.arrAirport=A2.airportId AND A2.city='Paris')` – Sick0ne Feb 23 '16 at 09:12

SQL intersection - error in syntax

2 Answers2