problèmes jointures

Le 16/12/2014 11:40, helios a écrit :

Le 16/12/2014 10:11, Damien Wyart a écrit :

mysql> SELECT * FROM villes;
+-------+---------------+
| insee | nom |
+-------+---------------+
| 02095 | BOHAIN |
| 02691 | SAINT QUENTIN |
| 02789 | VERVINS |
| 59122 | CAMBRAI |
| 59349 | LIGNY |
| 59392 | MAUBEUGE |
| 60159 | COMPIEGNE |
| 60175 | CREIL |
| 60612 | SENLIS |
| 62119 | BETHUNE |
| 62498 | LENS |
| 80261 | DURY |
| 80620 | PERONNE |
| 80685 | ROYE |
| 97304 | KOUROU |
| 97419 | SAINTE ROSE |
+-------+---------------+

mysql> SELECT * FROM dept;
+--------+---------------+----------+
| numero | nom | pref |
+--------+---------------+----------+
| 02 | AISNE | LAON |
| 59 | NORD | LILLE |
| 60 | OISE | BEAUVAIS |
| 62 | PAS DE CALAIS | ARRAS |
| 80 | SOMME | AMIENS |
| 973 | GUYANNE | CAYENNE |
| 974 | REUNION | ST DENIS |
+--------+---------------+----------+

mysql> SELECT villes.nom, pref FROM villes, dept WHERE (LEFT(insee, 2)
<> "97" AND LEFT(insee, 2) = numero) OR (LEFT(insee, 2) = "97" AND
LEFT(insee,3) = numero) ORDER BY nom;
+---------------+----------+
| nom | pref |
+---------------+----------+
| BETHUNE | ARRAS |
| BOHAIN | LAON |
| CAMBRAI | LILLE |
| COMPIEGNE | BEAUVAIS |
| CREIL | BEAUVAIS |
| DURY | AMIENS |
| KOUROU | CAYENNE |
| LENS | ARRAS |
| LIGNY | LILLE |
| MAUBEUGE | LILLE |
| PERONNE | AMIENS |
| ROYE | AMIENS |
| SAINT QUENTIN | LAON |
| SAINTE ROSE | ST DENIS |
| SENLIS | BEAUVAIS |
| VERVINS | LAON |
+---------------+----------+

ce n'est pas ce qui a été posé comme question dans la question c'est le
code INSEE de la prefecture qui est dans la table dept

- Villes :
vi_Code_insee
vi_Nom

- Departements :
dp_Numero
dp_Nom
dp_Code_insee_préfecture

vous n'avez donc pas répondu à la question

voici le debut de réponse attendu en sql il vous reste a complété par la
requete sql

mysql> SELECT * FROM villes;
+-------+---------------+
| insee | nom |
+-------+---------------+
| 02095 | BOHAIN |
| 02408 | LAON |
| 02691 | SAINT QUENTIN |
| 02789 | VERVINS |
| 59122 | CAMBRAI |
| 59349 | LIGNY |
| 59350 | LILLE |
| 59392 | MAUBEUGE |
| 60057 | BEAUVAIS |
| 60159 | COMPIEGNE |
| 60175 | CREIL |
| 60612 | SENLIS |
| 62041 | ARRAS |
| 62119 | BETHUNE |
| 62498 | LENS |
| 80021 | AMIENS |
| 80261 | DURY |
| 80620 | PERONNE |
| 80685 | ROYE |
| 97302 | CAYENNE |
| 97304 | KOUROU |
+-------+---------------+

mysql> SELECT * FROM dept;
+--------+---------------+----------+
| numero | nom | pref |
+--------+---------------+----------+
| 02 | AISNE | 02408 |
| 59 | NORD | 59350 |
| 60 | OISE | 60057 |
| 62 | PAS DE CALAIS | 62041 |
| 80 | SOMME | 80021 |
| 973 | GUYANNE | 97302 |
+--------+---------------+----------+

et voici le résultat attendu

+---------------+----------+
| nom | pref |
+---------------+----------+
| AMIENS | AMIENS |
| ARRAS | ARRAS |
| BEAUVAIS | BEAUVAIS |
| BETHUNE | ARRAS |
| BOHAIN | LAON |
| CAMBRAI | LILLE |
| COMPIEGNE | BEAUVAIS |
| CREIL | BEAUVAIS |
| CAYENNE | CAYENNE |
| DURY | AMIENS |
| KOUROU | CAYENNE |
| LAON | LAON |
| LENS | ARRAS |
| LIGNY | LILLE |
| LILLE | LILLE |
| MAUBEUGE | LILLE |
| PERONNE | AMIENS |
| ROYE | AMIENS |
| SAINT QUENTIN | LAON |
| SAINTE ROSE | ST DENIS |
| SENLIS | BEAUVAIS |
| VERVINS | LAON |
+---------------+----------+

* helios <helios@com02.net> in fr.comp.applications.sgbd:

ce n'est pas ce qui a été posé comme question dans la question c'est
le code INSEE de la prefecture qui est dans la table dept

mysql> SELECT * FROM villes LIMIT 10;
+-------+-----------------------+
| insee | nom |
+-------+-----------------------+
| 01001 | ABERGEMENT-CLEMENCIAT |
| 01002 | ABERGEMENT-DE-VAREY |
| 01004 | AMBERIEU-EN-BUGEY |
| 01005 | AMBERIEUX-EN-DOMBES |
| 01006 | AMBLEON |
| 01007 | AMBRONAY |
| 01008 | AMBUTRIX |
| 01009 | ANDERT-ET-CONDON |
| 01010 | ANGLEFORT |
| 01011 | APREMONT |
+-------+-----------------------+

mysql> SELECT * FROM dept LIMIT 10;
+--------+-------------------------+-------+
| numero | nom | pref |
+--------+-------------------------+-------+
| 01 | AIN | 01053 |
| 02 | AISNE | 02408 |
| 03 | ALLIER | 03190 |
| 04 | ALPES-DE-HAUTE-PROVENCE | 04070 |
| 05 | HAUTES-ALPES | 05061 |
| 06 | ALPES-MARITIMES | 06088 |
| 07 | ARDECHE | 07186 |
| 08 | ARDENNES | 08105 |
| 09 | ARIEGE | 09122 |
| 10 | AUBE | 10387 |
+--------+-------------------------+-------+

mysql> SELECT villes.nom, (SELECT nom FROM villes WHERE insee = pref) AS pref FROM villes, dept WHERE (LEFT(insee, 2) <> "97" AND LEFT(insee, 2) = numero) OR (LEFT(insee, 2) = "97" AND LEFT(insee,3) = numero) ORDER BY nom LIMIT 10;
+----------------------+------------+
| nom | pref |
+----------------------+------------+
| AAST | PAU |
| ABAINVILLE | BAR-LE-DUC |
| ABANCOURT | BEAUVAIS |
| ABANCOURT | LILLE |
| ABAUCOURT | NANCY |
| ABAUCOURT-HAUTECOURT | BAR-LE-DUC |
| ABBANS-DESSOUS | BESANCON |
| ABBANS-DESSUS | BESANCON |
| ABBARETZ | NANTES |
| ABBECOURT | BEAUVAIS |
+----------------------+------------+

--
DW

Le Tue, 16 Dec 2014 07:35:04 +0100,
Damien Wyart <damien.wyart@free.fr> écrivait :

Extrait de l'entête des messages publiés des "deux (un)" protagoniste(s) :
NNTP-Posting-Host: 88.175.144.136

Vous en tirerez les conclusions...

Bien vu, merci !

Vu l'état du fil je vais quand même poster une solution courte en SQL,
ça peut toujours servir à d'autres qui parcoureront les acrhives...
Parce que là, on a un peu l'impression d'après certaines réponses que
c'est un problème difficile alors que c'est élémentaire...

Je me demandais justement ce qu'il était devenu...

JKB

--
Si votre demande me parvient sur carte perforée, je titiouaillerai très
volontiers une réponse...
=> http://grincheux.de-charybde-en-scylla.fr
=> http://loubardes.de-charybde-en-scylla.fr

* helios <helios@com02.net> in fr.comp.applications.sgbd:

quel est le temps d'excution de la requête?

En activant le profiling dans MySQL (set profiling=1; show profiles;),
j'obtiens des temps de l'ordre de quelques millisecondes. Je n'ai pas
mis en place d'optimisation particulière (aucun index n'a été créé).

autrement une chose me semble étrange dans le résultat SQL c'est que
un tri par ordre alphabétique des préfecture s'est mysterieusement
fait pourles villes homonyme comme ABANCOURT alors qu'elle aurais du
rester dans l'ordre INSEE ce qui sent le bidouillage du résultat en
effet comment une requête peut faire un tri qui n'y figure pas ?

En SQL, ORDER BY sur des valeurs identiques n'a pas de comportement
défini, et on ne peut donc pas faire de telles suppositions. Il n'y
a pas eu de tri particulier mais un ordre indéfini. Si l'on veut un
critère de tri secondaire, il faut le spécifier explicitement :

mysql> SELECT villes.nom, (SELECT nom FROM villes WHERE pref = insee) AS pref FROM villes, dept WHERE (LEFT(insee, 2) <> "97" AND LEFT(insee, 2) = numero) OR (LEFT(insee, 2) = "97" AND LEFT(insee,3) = numero) ORDER BY nom, insee LIMIT 10;
+----------------------+------------+
| nom | pref |
+----------------------+------------+
| AAST | PAU |
| ABAINVILLE | BAR-LE-DUC |
| ABANCOURT | LILLE |
| ABANCOURT | BEAUVAIS |
| ABAUCOURT | NANCY |
| ABAUCOURT-HAUTECOURT | BAR-LE-DUC |
| ABBANS-DESSOUS | BESANCON |
| ABBANS-DESSUS | BESANCON |
| ABBARETZ | NANTES |
| ABBECOURT | LAON |
+----------------------+------------+

--
DW

Le 16-12-2014, JKB nous expliquait dans
fr.comp.applications.sgbd
(<slrnm90f22.8ld.jkb@rayleigh.systella.fr>) :

On t'avait pourtant expliqué qu'il ne fallait pas invoquer le diable /o

--
Tu rayes les mentions inutiles
Au bas de ton carnet d'absence
Et tu t'accroches au bout du fil
Qui te ramène à ton silence
-- H.F. Thiéfaine, Narcisse 81

Le Tue, 16 Dec 2014 15:52:32 +0100,
Doug713705 <doug.letough@free.fr> écrivait :

Le 16-12-2014, JKB nous expliquait dans
fr.comp.applications.sgbd
(<slrnm90f22.8ld.jkb@rayleigh.systella.fr>) :

On t'avait pourtant expliqué qu'il ne fallait pas invoquer le diable /o

J'oublie toujours :-(

JKB

--
Si votre demande me parvient sur carte perforée, je titiouaillerai très
volontiers une réponse...
=> http://grincheux.de-charybde-en-scylla.fr
=> http://loubardes.de-charybde-en-scylla.fr

* helios <helios@com02.net> in fr.comp.applications.sgbd:

c'est justement le fait qu'il ne doit pas y avoir de tri et qu'il y en
a un qui rends les choses suspectes

Comme je l'ai déjà écrit, il n'y a pas de tri particulier sur la seconde
colonne. Le nom ayant le plus d'occurrences est SAINTE COLOMBE. Avec ma
requête ayant un seul critère de tri, j'obtiens ceci :

SAINTE-COLOMBE RENNES
SAINTE-COLOMBE MELUN
SAINTE-COLOMBE ROUEN
SAINTE-COLOMBE SAINT-LO
SAINTE-COLOMBE BORDEAUX
SAINTE-COLOMBE CAHORS
SAINTE-COLOMBE ANGOULEME
SAINTE-COLOMBE MONT-DE-MARSAN
SAINTE-COLOMBE ROCHELLE
SAINTE-COLOMBE DIJON
SAINTE-COLOMBE BESANCON
SAINTE-COLOMBE GAP
SAINTE-COLOMBE LYON
SAINTE-COLOMBE AUXERRE

On voit bien que l'ordre sur les noms de préfectures est quelconque.
Avec deux occurrences comme sur les cas que vous citez, il n'y a que
deux ordres possibles, ce qui ne montre pas qu'il y a eu un tri.

Avec la requête qui trie sur deux critères, on obtient :

SAINTE-COLOMBE GAP
SAINTE-COLOMBE ANGOULEME
SAINTE-COLOMBE ROCHELLE
SAINTE-COLOMBE DIJON
SAINTE-COLOMBE BESANCON
SAINTE-COLOMBE BORDEAUX
SAINTE-COLOMBE RENNES
SAINTE-COLOMBE MONT-DE-MARSAN
SAINTE-COLOMBE CAHORS
SAINTE-COLOMBE SAINT-LO
SAINTE-COLOMBE LYON
SAINTE-COLOMBE ROUEN
SAINTE-COLOMBE MELUN
SAINTE-COLOMBE AUXERRE

Et les noms de préfectures sont bien triés selon leur numéro INSEE.

--
DW

mysql> SELECT villes.nom, (SELECT nom FROM villes WHERE pref = insee)
AS pref FROM villes, dept WHERE (LEFT(insee, 2) <> "97" AND
LEFT(insee, 2) = numero) OR (LEFT(insee, 2) = "97" AND LEFT(insee,3) > numero) ORDER BY nom, insee LIMIT 10;
+----------------------+------------+
| nom | pref |
+----------------------+------------+
| AAST | PAU |
| ABAINVILLE | BAR-LE-DUC |
| ABANCOURT | LILLE |
| ABANCOURT | BEAUVAIS |
| ABAUCOURT | NANCY |
| ABAUCOURT-HAUTECOURT | BAR-LE-DUC |
| ABBANS-DESSOUS | BESANCON |
| ABBANS-DESSUS | BESANCON |
| ABBARETZ | NANTES |
| ABBECOURT | LAON |
+----------------------+------------+

Pour information, un SELECT imbriqué dans un autre SELECT (avant le
FROM) comme je l'ai proposé n'est pas accepté par tous les moteurs SQL,
notamment PostgreSQL. Une solution plus standard serait plutôt (j'ai
renommé les colonnes "nom" pour éviter les ambiguïtés lors de la
jointure) :

mysql> SELECT * FROM villes LIMIT 10;
+-------+-----------------------+
| insee | nom_v |
+-------+-----------------------+
| 01001 | ABERGEMENT-CLEMENCIAT |
| 01002 | ABERGEMENT-DE-VAREY |
| 01004 | AMBERIEU-EN-BUGEY |
| 01005 | AMBERIEUX-EN-DOMBES |
| 01006 | AMBLEON |
| 01007 | AMBRONAY |
| 01008 | AMBUTRIX |
| 01009 | ANDERT-ET-CONDON |
| 01010 | ANGLEFORT |
| 01011 | APREMONT |
+-------+-----------------------+

mysql> SELECT * FROM dept LIMIT 10;
+--------+-------------------------+-------+
| numero | nom_d | pref |
+--------+-------------------------+-------+
| 01 | AIN | 01053 |
| 02 | AISNE | 02408 |
| 03 | ALLIER | 03190 |
| 04 | ALPES-DE-HAUTE-PROVENCE | 04070 |
| 05 | HAUTES-ALPES | 05061 |
| 06 | ALPES-MARITIMES | 06088 |
| 07 | ARDECHE | 07186 |
| 08 | ARDENNES | 08105 |
| 09 | ARIEGE | 09122 |
| 10 | AUBE | 10387 |
+--------+-------------------------+-------+

mysql> SELECT villes.nom_v, prefs.nom_v FROM villes, (SELECT numero, nom_v FROM dept, villes WHERE pref = insee) AS prefs WHERE (LEFT(villes.insee, 2) <> "97" AND LEFT(villes.insee, 2) = numero) OR (LEFT(villes.insee, 2) = "97" AND LEFT(villes.insee,3) = numero) ORDER BY villes.nom_v, villes.insee LIMIT 10;
+----------------------+------------+
| nom_v | nom_v |
+----------------------+------------+
| AAST | PAU |
| ABAINVILLE | BAR-LE-DUC |
| ABANCOURT | LILLE |
| ABANCOURT | BEAUVAIS |
| ABAUCOURT | NANCY |
| ABAUCOURT-HAUTECOURT | BAR-LE-DUC |
| ABBANS-DESSOUS | BESANCON |
| ABBANS-DESSUS | BESANCON |
| ABBARETZ | NANTES |
| ABBECOURT | LAON |
+----------------------+------------+

--
DW

mysql> SELECT villes.nom_v, prefs.nom_v FROM villes, (SELECT numero,
nom_v FROM dept, villes WHERE pref = insee) AS prefs WHERE
(LEFT(villes.insee, 2) <> "97" AND LEFT(villes.insee, 2) > numero) OR (LEFT(villes.insee, 2) = "97" AND
LEFT(villes.insee,3) = numero) ORDER BY villes.nom_v,
villes.insee LIMIT 10;
+----------------------+------------+
| nom_v | nom_v |
+----------------------+------------+
| AAST | PAU |
| ABAINVILLE | BAR-LE-DUC |
| ABANCOURT | LILLE |
| ABANCOURT | BEAUVAIS |
| ABAUCOURT | NANCY |
| ABAUCOURT-HAUTECOURT | BAR-LE-DUC |
| ABBANS-DESSOUS | BESANCON |
| ABBANS-DESSUS | BESANCON |
| ABBARETZ | NANTES |
| ABBECOURT | LAON |
+----------------------+------------+

Un peu plus lisible et identique au résultat de ma requête précédente :

mysql> SELECT villes.nom_v, prefs.nom_v AS pref FROM villes, (SELECT numero,
nom_v FROM dept, villes WHERE pref = insee) AS prefs WHERE
(LEFT(villes.insee, 2) <> "97" AND LEFT(villes.insee, 2) numero) OR (LEFT(villes.insee, 2) = "97" AND
LEFT(villes.insee,3) = numero) ORDER BY villes.nom_v,
villes.insee LIMIT 10;
+----------------------+------------+
| nom_v | pref |
+----------------------+------------+
| AAST | PAU |
| ABAINVILLE | BAR-LE-DUC |
| ABANCOURT | LILLE |
| ABANCOURT | BEAUVAIS |
| ABAUCOURT | NANCY |
| ABAUCOURT-HAUTECOURT | BAR-LE-DUC |
| ABBANS-DESSOUS | BESANCON |
| ABBANS-DESSUS | BESANCON |
| ABBARETZ | NANTES |
| ABBECOURT | LAON |
+----------------------+------------+

Cette forme est également plus rapide car elle n'effectue pas le SELECT
imbriqué pour chaque ville (même si les optimisations internes font que
ma requête avec imbrication dans le SELECT restait quand même rapide
d'un point de vue utilisateur). En mode profiling, j'obtiens des temps
d'exécution de l'ordre de quelques dizaines de micro-secondes pour cette
imbrication au niveau du FROM.

--
DW

En activant le profiling dans MySQL (set profiling=1; show profiles;),
j'obtiens des temps de l'ordre de quelques millisecondes. Je n'ai pas
mis en place d'optimisation particulière (aucun index n'a été créé).

[...]
Cette forme est également plus rapide car elle n'effectue pas le
SELECT imbriqué pour chaque ville (même si les optimisations internes
font que ma requête avec imbrication dans le SELECT restait quand même
rapide d'un point de vue utilisateur). En mode profiling, j'obtiens
des temps d'exécution de l'ordre de quelques dizaines de
micro-secondes pour cette imbrication au niveau du FROM.

J'ai réalisé mes tests avec MySQL 5.5 qui utilise un cache interne de
requêtes. Lors du rejeu de la même requête, les temps sont très faibles,
ainsi que la moyenne des temps sur plusieurs exécutions.

En désactivant ce cache (il est désactivé par défaut avec MySQL 5.6),
j'obtiens environ 0.5 secondes pour chacune des deux requêtes (pas de
différence significative).

--
DW