Discussion :

[escartefigue]

Voici un exemple (la dernière requête ci-dessous) qui permet d'extraire toutes les étapes des itinéraires passant à la fois par Biarritz et La Rochelle

Code SQL :

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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
create table IT_itineraire 
      (  IT_ident  integer       auto_increment primary key
       , IT_code   char(4)       not null   unique
       , IT_nom    varchar(128)  not null
      )
;  
create table VI_ville
      (  VI_ident  integer       auto_increment primary key
       , VI_insee  char(5)       not null
       , VI_nom    varchar(50)   not null
      )
;  
create table ET_etape
      (  IT_ident  integer       not null
       , ET_num    smallint      not null
       , VI_ident  integer       not null
       , constraint ETPK  
         primary key (IT_ident, ET_num)
       , constraint ETFK1 
         foreign key (IT_ident)
         references IT_itineraire(IT_ident)
       , constraint ETFK2
         foreign key (VI_ident)
         references VI_ville(VI_ident)
      )
;
insert into VI_ville 
       (VI_insee, VI_nom)
values ('33063', 'Bordeaux')
     , ('29019', 'Brest')
     , ('17300', 'La Rochelle')
     , ('64122', 'Biarritz')
     , ('64260', 'Hendaye')
     , ('64485', 'Saint-Jean-Pied-de-Port')  
;
select * from VI_ville
;
insert into IT_itineraire
       (IT_code, IT_nom)
values ('ATL1', 'La côte atlantique du nord au sud')
     , ('ATL2', 'La côte atlantique du sud au nord')
     , ('TBPA', 'Le tour du Pays Basque')
;
select * from IT_itineraire
;
insert into ET_etape
       (IT_ident, ET_num, VI_ident)
values (1, 1, 2)
     , (1, 2, 3)
     , (1, 3, 1)
     , (1, 4, 4)
     , (1, 5, 5)
     , (2, 1, 5)
     , (2, 2, 4)
     , (2, 3, 3)
     , (2, 4, 1)
     , (2, 5, 2)
     , (3, 1, 5)
     , (3, 2, 4)
     , (3, 3, 6)
     , (3, 4, 5)
;
select IT.IT_code as "Ref"  
     , IT.IT_nom  as "Itinéraire"  
     , ET.ET_num  as "n° etape"
     , VI.VI_nom  as "Ville"
from IT_itineraire   as IT
inner join ET_etape  as ET 
   on ET.IT_ident = IT.IT_ident
inner join VI_ville  as VI
   on VI.VI_ident = ET.VI_ident
order by IT.IT_code
       , ET.ET_num
;
-- recherche des itinéraires passant à la fois par Biarritz et La Rochelle
select IT.IT_code as "Ref"  
     , IT.IT_nom  as "Itinéraire"  
     , ET.ET_num  as "n° etape"
     , VI.VI_nom  as "Ville"
from IT_itineraire   as IT
inner join ET_etape  as ET 
   on ET.IT_ident = IT.IT_ident
inner join VI_ville  as VI
   on VI.VI_ident = ET.VI_ident
where exists
     (select 1
      from IT_itineraire   I1
      inner join ET_etape  E1
         on E1.IT_ident = I1.IT_ident
      inner join VI_ville  V1
         on V1.VI_ident = E1.VI_ident  
      where I1.IT_ident=IT.IT_ident
        and V1.VI_nom='Biarritz'
     )
  and exists
     (select 1
      from IT_itineraire   I1
      inner join ET_etape  E1
         on E1.IT_ident = I1.IT_ident
      inner join VI_ville  V1
         on V1.VI_ident = E1.VI_ident  
      where I1.IT_ident=IT.IT_ident
        and V1.VI_nom='La Rochelle'
     )
order by IT.IT_code
       , ET.ET_num
 
;

Résultat de la dernière requête :

[chile]

Bonjour Escartefigue,

Merci beaucoup pour ta réponse.

J'ai appliqué ton principe avec mes tables.

Je suis en train de tester EXISTS sur un seul lieux pour commencer, mais il ne filtre pas.

J'ai bien un affichage, mais de la totalité des circuits.

J'ai pourtant un seul circuit sur dix, avec '1' dans ma colonne devis_itineraires_lieux.bordeaux

Vois tu ce qu'il me manque ?

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
"SELECT devis_itineraires_versions.code_circuit, devis_itineraires_versions.version, 
devis_itineraires_lieux.code_circuit, 
devis_itineraires_lieux.version, 
devis_itineraires_lieux.bordeaux, 
devis_itineraires_lieux.marseille, 
devis_itineraires_lieux.lyon, 
devis_itineraires_lieux.paris, 
 
FROM devis_itineraires_versions
INNER JOIN devis_itineraires_lieux ON devis_itineraires_versions.code_circuit = devis_itineraires_lieux.code_circuit
AND devis_itineraires_versions.version = devis_itineraires_lieux.version
 
 WHERE EXISTS (
 SELECT bordeaux 
 FROM devis_itineraires_lieux 
 WHERE devis_itineraires_lieux.bordeaux = 1
 )
 
  ORDER BY date DESC"

[chile]

En fait je bloque toujorus pour la même raison.

Je n'arrive à faire une requete avec la condition de n'afficher que les circuits qui ont AU MOINS 'Paris' et 'Marseille', par exemple.

[Séb.]

escartefigue t'a donné un exemple qui répond à ce besoin.

Problème : tu utilises mal EXISTS.

Tu fais :

WHERE EXISTS (
    SELECT bordeaux 
    FROM devis_itineraires_lieux 
    WHERE devis_itineraires_lieux.bordeaux = 1
)

=> "Est-ce que 1 itinéraire quelconque passe par Bordeaux ?"

Alors qu'il faudrait faire :

and exists (
    select 1
    from IT_itineraire I1
    inner join ET_etape E1 on E1.IT_ident = I1.IT_ident
    inner join VI_ville V1 on V1.VI_ident = E1.VI_ident  
    where true
        -- Ce prédicat est important, tu vérifies si tel itinéraire (sous-requête corrélée à IT)...
        and I1.IT_ident = IT.IT_ident
        -- ... passe par telle ville
        and V1.VI_nom = 'La Rochelle'
)

Respecte l'exemple d'escaretfigue et ça devrait être bon.

[escartefigue]

Bonjour,

@Chile : votre requête ne correspond pas à ma proposition, relisez ce que j'ai publié dans ma réponse du 1er mars.
Egalement, la restriction WHERE devis_itineraires_lieux.bordeaux = 1 montre que vous n'avez pas tenu compte de ma très forte réserve quant à votre modèle de données, pour rappel :

Ce type de modélisation est à proscrire : mettre en colonnes les différentes villes complique inutilement les évolutions et compromet les jointures.

• Le jour où de nouvelles destinations sont à gérer, il faut modifier la table et les objets associés (vues, triggers, procédures...)
• L'indexation sera hyper gourmande puisqu'il faut autant d'index que de colonnes destination
• Dans chaque circuit enregistré dans la table, la plupart des destinations sont inutiles, on encombre donc inutilement l'espace disque
• Le principe d'un circuit c'est non seulement de connaitre les étapes, mais aussi dans quel ordre elles sont et, souvent, à quelle date voire heure elles sont prévues

Bref, rien ne va, il faut utiliser la modélisation que j'ai communiquée plus haut, elle permettra de satisfaire votre besoin, sera indolore le jour où de nouvelles destinations seront à gérer, les performances seront accrues et les requêtes seront bien plus simples.

J'ai expliqué la bonne modélisation à utiliser dans ma réponse du 11 janvier.