Discussion :

[totot]

Bonjour,

Je cherche à faire une requête sql qui m'affiche tout les pilote qui n'ont pas était a Annecy. J'ai donc fait la requête suivante mais celle-ci ne me prend pas les pilote qui ne sont pas présent dans la table VOL or elle doit les prendre car ils ont jamais étaient à Annecy.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT nom, prenom, MISSION.idMission, villeDepart, villeArrivee
FROM PERSONNEL
LEFT JOIN VOL ON PERSONNEL.idPersonnel = VOL.idPilote
JOIN MISSION ON MISSION.idMission = VOL.idMission
JOIN ESCALE ON ESCALE.idMission = MISSION.idMission
WHERE (villeArrivee != 'Annecy' 
OR ville != 'Annecy')
AND fonction = 'Pilote';

Merci de votre aide

[escartefigue]

IL faudrait qualifier la table pour chaque colonne car la provenance des données n'est pas claire, mais la requete pourrait ressembler à ceci
(j'ai supposé que les villes étaient dans la table "VOL")

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT PRS.nom, PRS.prenom, MIS.idMission, VOL.villeDepart, VOL.villeArrivee
FROM PERSONNEL as PRS
INNER JOIN VOL 
  ON PRS.idPersonnel = VOL.idPilote
INNER JOIN MISSION as MIS
  ON MIS.idMission = VOL.idMission
WHERE NOT EXISTS
     (SELECT 1 FROM VOL as SUBQ
      WHERE SUBQ.idPilote = PRS.idPersonnel
        AND (    SUBQ.villeDepart  ='ANNECY'
             OR  SUBQ.villeArrivee ='ANNECY')
      )

[totot]

Les ville sont dans la table mission. J'ai essayé de faire ceci mais il me manque toujours un pilote:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT nom, prenom, idPersonnel
FROM PERSONNEL
WHERE idPersonnel NOT IN (SELECT idPilote FROM VOL JOIN PERSONNEL ON PERSONNEL.idPersonnel = VOL.idPilote)
AND fonction = 'Pilote'
MINUS
SELECT nom, prenom, idPersonnel
FROM PERSONNEL
JOIN VOL ON PERSONNEL.idPersonnel = VOL.idPilote
JOIN MISSION ON VOL.idMission = MISSION.idMission
JOIN ESCALE ON MISSION.idMission = ESCALE.idMission
WHERE (villeArrivee != 'Annecy'
OR ville != 'Annecy')
AND villeArrivee != 'Annecy';

Je ne vois pas ce qu'il manque ou se qui cloche

[escartefigue]

Tu ne peux pas trouver les pilotes n'ayant pas été a Annecy en faisant un where ville différent de Annecy :

si par exemple un pilote a fait un vol vers Toulouse, puis un autre vers Annecy, alors tu vas le sélectionner à cause du 1er vol.

Ceci devrait correspondre au besoin :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[totot]

Sa ne fonctionne car il m'affiche des gens qui sont déjà aller à Annecy

[escartefigue]

Ne serait ce pas simplement un problème de majuscules/minuscules ?
En ce cas il suffit d'ajouter la conversion en majuscules (upper ou uppercase selon les sgbd) comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 (    UPPER(VOL1.villeDepart)  ='ANNECY'
                OR  UPPER(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 UPPER(ESC2.ville)  ='ANNECY'
        )

Sinon, il faut que tu détailles le résultat obtenu et dans quelle(s) table(s) chacune des villes est présente :
- ville
- villeDepart
- villeArrivee

[totot]

En effet, il y avait un problème de majuscule / minuscule.
En revanche, il ne m'affiche qu'un seul pilote. C'est bien le bon mais il ne m'affiche pas également ceux qui sont dans la table personnel et qui ne se trouvent pas dans la table vol

[SQLpro]

Plutôt que d'utiliser une merdouille avec UPPER, utilisez une COLLATION, c'est SARGABLE !

Pour les pilotes n'ayant jamais volé, faite un EXCEPT et une concaténation avec UNION ALL

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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   COLLATE French_CI_AI = 'ANNECY'
                OR  VOL1.villeArrivee COLLATE French_CI_AI = '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  COLLATE French_CI_AI ='ANNECY'
        )
UNION ALL
SELECT PRS.nom, PRS.prenom, NULL, NULL, NULL
FROM PERSONNEL as PRS
WHERE FONCTION = 'Pilote' --> ou autre chose !!!
EXCEPT
SELECT PRS.nom, PRS.prenom, NULL, NULL, NULL
FROM PERSONNEL as PRS
     INNER JOIN VOL 
     ON VOL.idPilote = PRS.idPersonnel 
WHERE FONCTION = 'Pilote'

A +

[escartefigue]

il me semble que COLLATE n'est pas implémenté en standard pour toutes les DB, me fourvoyai-je ?

[SQLpro]

Tous les SGBDR possèdent le COLLATE avec plus ou moins d'options, sauf Oracle avec cette innommable merde de NLS !

A +