Jointure sur champ non unique.

Version imprimable

Bonjour,

Je sais qu'il s'agit d'un cas d'école mais voilà je bloque! Je vous expose mon problème :

Voici 2 tables :

Code:

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
 
CREATE TABLE adresse (
  id_adresse INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  nom  VARCHAR(50) NULL,
  prenom VARCHAR(50) NULL,
  code_postal INTEGER(4) UNSIGNED NULL,
  localite VARCHAR(50) NULL,
  PRIMARY KEY(id_adresse)
);
 
INSERT INTO `adresse` (`id_adresse`, `nom`, `prenom`, `code_postal`, `localite`) VALUES 
(1, 'DUPONT', 'Frédéric', 7800, 'Ath'),
(2, 'GREGOIRE', 'Blaise', 7801, 'Irchonwelz');
 
CREATE TABLE province (
  id_province INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  code_postal INTEGER(4) UNSIGNED NOT NULL,
  localite VARCHAR(50) NOT NULL,
  province VARCHAR(30) NOT NULL,
  PRIMARY KEY(id_province)
);
 
INSERT INTO `province` (`id_province`, `code_postal`, `localite`, `province`) VALUES 
(1, 7800, 'Ath', 'Hainaut'),
(2, 7800, 'Lanquesaint', 'Hainaut'),
(3, 7801, 'Irchonwelz', 'Hainaut');

J'aimerai juste obtenir le nombre d'adresse par province en réalisant une jointure sur code_postal (qui n'est pas unique).

Voici la requête que j'utilise :

Code:

1
2
3
4
5
 
SELECT t1.province, COUNT(t2.id_adresse) AS "nbr adresse"
FROM province AS t1
INNER JOIN adresse AS t2 USING(code_postal)
GROUP BY t1.province

Vous l'aurez déjà tous compris, le résultat de ma requête est :
province nbr adresse
Hainaut 3

Je cherche à obtenir le bon résultat en une et unique requête... Pourriez vous m'aider?

++

27/05/2008, 15h53
al1_24

Il suffit de faire la jointure sur code_postal et localité.
27/05/2008, 16h19
defcon_suny

Ca ce serait trop facile...
Le champ localite de la table adresse est complété par les utilisateurs et donc je ne peux pas m'y fier. Le champ localite de la table province est lui plus "officielle".

Une autre idée?

je n'aime pas les distinct, mais la modélisation de la base nous oblige à les utiliser dans ce cas.
Code:

1 2 3 4 5 6 7 select province, count(*) from ( select distinct province, id_adresse from adresse join province on adresse.code_postal = province.codepostal) group by province

une solution parmis tant d'autres ...

Code:

1
2
3
4
5
6
7
 
SELECT province, count(1) 
FROM
  (SELECT adresse.id_adresse,province.province,count(1)
    FROM province INNER JOIN  adresse ON   (province.code_postal=adresse.code_postal) 
   group by province.province ) a
group by province

Bon courage

Autre solution :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT    t1.province
    ,     COUNT(t2.id_adresse) AS "nbr adresse"
FROM     (    SELECT    province
                ,    code_postal
            FROM    province
            GROUP BY
                    province
                ,    code_postal
        )    AS t1
    INNER JOIN 
        adresse AS t2 
        USING(code_postal)
GROUP BY t1.province
;

Al1_24, à quoi sert cette partie de la requête ?

Code:

1
2
3
4
5
6
SELECT    province
                ,    code_postal
            FROM    province
            GROUP BY
                    province
                ,    code_postal

Un tout grand merci! je n'avais pas pensé à imbriquer... comme quoi parfois!!!!

Voici donc une solution la solution fonctionnelle à mon problème :
Code:

1 2 3 4 5 6 7 8 SELECT province AS p, count( * ) as nbr FROM ( SELECT DISTINCT t2.province, t1.id_adresse FROM adresse AS t1 JOIN province AS t2 ON t1.code_postal = t2.code_postal ) AS t3 GROUP BY p
Merci à vous 2!!!

27/05/2008, 17h24
al1_24

@ Monstros :
Le problème étant que la table province présente des doublons sur (code_postal, province), le regroupement permet de n'avoir qu'une ligne par code_postal.
27/05/2008, 17h31
defcon_suny

Effectivement, ce qui rend dérisoire la clause DISTINCT.

Bien vu!
27/05/2008, 17h32
Yanika_bzh

DISTINCT est a proscrire
27/05/2008, 17h33
defcon_suny

@ Yanika
Peux tu en dire un peu plus?
27/05/2008, 17h36
Yanika_bzh

L'utilisation d'un DISTINCT signifie que la clause n'est pas assez restrictive.
Et une clause qui n'est pas assez restrictive va faire plomber vos performances.
28/05/2008, 09h00
defcon_suny

Bon raisonnement.
Existe t'il une méthode ou une bonne pratique dans la construction des restrictions d'une requête pour éviter les distinct?
28/05/2008, 10h24
al1_24

Citation:

Envoyé par defcon_suny

Existe-t-il une méthode ou une bonne pratique dans la construction des restrictions d'une requête pour éviter les distinct?

Rien de plus que la réflexion ;)
28/05/2008, 10h34
Monstros Velu

Bien vu pour le group by à la place du distinct.

Je sais bien qu'il ne faut pas utiliser le "distinct", parce que quand la base est bien modélisée, on n'en a jamais besoin. Mais là, elle n'est pas bien modélisée, et je ne connaissais pas l'astuce du group by à la place du distinct.

D'ailleurs, est-ce réellement plus performant ? Et si c'est plus performant, ne ferait-ils pas mieux de l'implémenter ainsi ?
28/05/2008, 11h59
defcon_suny

Je ne sais pas non plus si la clause DISTINCT est plus performante que le GROUP BY dans ce cas ou le contraire. Mais dans le cadre de bonnes pratiques, laissons de côté le DISTINCT au risque de perdre un peu en rapidité...

Merci à vous pour votre participation!
28/05/2008, 15h16
Monstros Velu

Si les bonnes pratiques font perdre en performance pour un même résultat et un travail équivalent, ce ne sont plus vraiment de bonnes pratiques; Du moins, celà mérite réflexion. Le principal étant de savoir ce qu'on fait ^^