Skip to content
Advertisement

select all passengers that landed in Palma de Mallorca or Barcelona but not both

I have to create a statement that fulfills the following requirement:

Provide a list of all passengers who have landed either at the airport “Palma de Mallorca” or in “Barcelona”. But by no means on both.

with this database: Flughafen-database

this is what i came up with:

select * 
from person
    join passagierliste using (personid)
    join flug using (flugid)
    join flughafen on flug.flughafen_destination=flughafen.flughafenid
where (flughafenid = 'Palma de Mallorca') xor (flughafenid = 'Barcelona')
;

but that throws the following error:

An error occurred when executing the SQL command: select * from person join passagierliste using (personid) join flug using (flugid) join flughafen on flug.flughafen_… ORA-00933: SQL command not properly ended [SQL State=42000, DB Errorcode=933]

Execution time: 0.03s

1 statement(s) failed.

Update

found an error but not the error (instead of flughafenid I should have used bezeichnung:

select * 
from person
    join passagierliste using (personid)
    join flug using (flugid)
    join flughafen on flug.flughafen_destination=flughafen.flughafenid
where (bezeichnung = 'Palma de Mallorca') xor (bezeichnung = 'Barcelona')
;

Advertisement

Answer

You can group by the personid and count the distinct descriptions (bezeichnung) in the group. Because of the WHERE clause, you know that only descriptions of Mallorca or Barcelona are selected. That means if there’s more than 1 distinct description per person, they’ve visited both. Use this (untested) as a CTE or subquery to get the relevant data from the person table.

select person.personid
from person
    join passagierliste using (personid)
    join flug using (flugid)
    join flughafen on flug.flughafen_destination=flughafen.flughafenid
where bezeichnung IN('Palma de Mallorca', 'Barcelona')
group by person.personid
having count(distinct bezeichnung) = 1
7 People found this is helpful
Advertisement