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