1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
|
SELECT PRS.nom, PRS.prenom, MIS.idMission, MIS.villeDepart, MIS.villeArrivee
FROM PERSONNEL as PRS
LEFT OUTER JOIN VOL
ON VOL.idPilote = PRS.idPersonnel
INNER JOIN MISSION as MIS
ON MIS.idMission = VOL.idMission
-- elimination des pilotes ayant volé au départ ou à destination d'Annecy
WHERE NOT EXISTS
(SELECT 1 FROM VOL as VOL1
WHERE SUBQ.idPilote = PRS.idPersonnel
AND ( VOL1.villeDepart ='ANNECY'
OR VOL1.villeArrivee ='ANNECY')
)
-- elimination des pilotes ayant fait escale à Annecy
AND NOT EXISTS
(SELECT 1 FROM ESCALE as ESC2
INNER JOIN MISSION as MIS2
ON MIS2.idMission = ESC2.idMission
INNER JOIN VOL as VOL2
ON VOL2.idMission = ESC2.idMission
WHERE VOL2.idPilote = PRS.idPersonnel
AND ESC2.ville ='ANNECY'
) |
Partager