Ressortir les count = 0 sur select sur jointure multiple

Version imprimable

1 pièce(s) jointe(s)

Bonjour à tous,

Je suis bloqué sur une requête qui me paraissait pourtant simple à la base...
Le but est d'afficher le compte de toutes les combinaisons de l'association de deux tables liées chacune par une table en one to many, les deux tables étant liées en many to many (c'est pourtant claire ;) )

Pièce jointe 199876

Il faudrait donc dans mon exemple afficher toutes les combinaison langue-couleur, puis le ombre de sous_produit associés, ou 0 en face

Voici ma base de test :

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
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
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
 
CREATE TABLE IF NOT EXISTS `produit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reference` varchar(127) NOT NULL,
  `fk_langue` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_langue` (`fk_langue`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
 
INSERT INTO `produit` (`id`, `reference`, `fk_langue`) VALUES
(3, 'A', 1),
(4, 'B', 2);
 
CREATE TABLE IF NOT EXISTS `produit_langue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txt` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
 
INSERT INTO `produit_langue` (`id`, `txt`) VALUES
(1, 'en'),
(2, 'fr'),
(3, 'de'),
(4, 'es');
 
CREATE TABLE IF NOT EXISTS `sous_produit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reference` varchar(127) NOT NULL,
  `fk_couleur` int(11) NOT NULL,
  `fk_produit` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_couleur` (`fk_couleur`),
  KEY `fk_produit` (`fk_produit`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
 
INSERT INTO `sous_produit` (`id`, `reference`, `fk_couleur`, `fk_produit`) VALUES
(1, '1', 1, 3),
(2, '2', 1, 3),
(3, '3', 1, 3),
(4, '4', 2, 3),
(5, '5', 3, 3),
(6, '6', 3, 3),
(7, '7', 3, 3),
(8, '8', 2, 4),
(9, '9', 3, 4),
(10, '10', 3, 4);
 
CREATE TABLE IF NOT EXISTS `sous_produit_couleur` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txt` varchar(127) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
 
INSERT INTO `sous_produit_couleur` (`id`, `txt`) VALUES
(1, 'bleu'),
(2, 'rouge'),
(3, 'jaune'),
(4, 'vert');
 
ALTER TABLE `produit`
  ADD CONSTRAINT `produit_fk_produit_langue` FOREIGN KEY (`fk_langue`) REFERENCES `produit_langue` (`id`);
 
ALTER TABLE `sous_produit`
  ADD CONSTRAINT `sous_produit_fk_produit` FOREIGN KEY (`fk_produit`) REFERENCES `produit` (`id`),
  ADD CONSTRAINT `sous_produit_fk_sous_produit_couleur` FOREIGN KEY (`fk_couleur`) REFERENCES `sous_produit_couleur` (`id`);

Voici ma requête qui ne fonctionne pas.

Code:

SELECT CONCAT(produit_langue.txt,' ',sous_produit_couleur.txt) AS 'categorie', COUNT(sous_produit.id) AS 'compte' FROM produit_langue LEFT JOIN produit ON produit.fk_langue = produit_langue.id INNER JOIN sous_produit ON sous_produit.fk_produit = produit.id RIGHT JOIN sous_produit_couleur ON sous_produit_couleur.id = sous_produit.fk_couleur WHERE 1 GROUP BY categorie

Ce qui ne marche pas du tout mais je ne comprends pas pourquoi...

EDIT : faute de frappe dans le titre :s

Hello !

tu as un problème au niveau de tes jointures.

essayes ça :

Code:

1
2
3
4
5
6
7
8
SELECT 
	CONCAT(produit_langue.txt, ' ', COALESCE(sous_produit_couleur.txt, 'sans couleur')) AS 'categorie', 
	COUNT(*) AS 'compte' 
FROM produit_langue 
	LEFT JOIN produit ON produit.fk_langue = produit_langue.id 
	LEFT JOIN sous_produit ON sous_produit.fk_produit = produit.id 
	LEFT JOIN sous_produit_couleur ON sous_produit_couleur.id = sous_produit.fk_couleur 
GROUP BY CONCAT(produit_langue.txt, ' ', COALESCE(sous_produit_couleur.txt, 'sans couleur'))

Salut à tous.

Il a plusieurs problèmes, pas nécessairement graves, que voici :
1) si vous utilisez un auto_incrément, il n'est pas nécessaire de mettre une valeur dans votre insert.
Autrement dit, à quoi sert de définir un auto_increment, si vous forcez la valeur.

2) il vaut mieux mettre les tables dans l'ordre de leur remplissage. Le nouvel ordre devient :
(a) --> Table Produit_langue
(b) --> Table Produit
(c) --> Table Sous_Produit_Couleur
(d) --> Table Sous_Produit

Il y a une dépendance entre 'b' et 'a' et aussi une dépendance entre 'd' et 'c', ainsi qu'entre 'd' et 'b'.

3) les foreign key sont mal définies ou c'est un oubli. Il manque à la fin "ON DELETE RESTRICT ON UPDATE RESTRICT".
Je préfère préciser le paramétrage dans sa totalité, même si ce sont des valeurs par défaut.
Et aussi, je préfère mettre les foreign key au moment de la création de la table et pas à la fin du script.

4) il y a aussi un ordre d'appel des jointures des tables. D'où l'apparition d'une ligne à NULL qui n'existe pas.

J'ai créé trois requêtes :
--> la première, c'est la jointure entre vos quatre tables.
--> la deuxième, c'est la solution que je propose.
--> la troisième, c'est la solution que vous utilisez (mathspountz) et qui ne fonctionne pas.

J'ai utilisez la jointure "left outer join", afin de faire apparaître toutes les lignes.
Ce n'était pas nécessaire car vous utilisez les "foreign key" et de ce fait, il ne peut pas exister une absence de valeur dans la jointure.

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
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
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"
--------------
 
--------------
SET time_zone = "+00:00"
--------------
 
--------------
CREATE TABLE IF NOT EXISTS `produit_langue`
( `id` int(11) NOT NULL AUTO_INCREMENT,
  `txt` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `produit_langue` (`id`, `txt`) VALUES
(1, 'en'),(2, 'fr'),(3, 'de'),(4, 'es')
--------------
 
--------------
select * from produit_langue
--------------
 
+----+-----+
| id | txt |
+----+-----+
|  1 | en  |
|  2 | fr  |
|  3 | de  |
|  4 | es  |
+----+-----+
--------------
CREATE TABLE IF NOT EXISTS `produit`
( `id` int(11) NOT NULL AUTO_INCREMENT,
  `reference` varchar(127) NOT NULL,
  `fk_langue` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `produit_fk_produit_langue` FOREIGN KEY (`fk_langue`) REFERENCES `produit_langue` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `produit` (`id`, `reference`, `fk_langue`) VALUES
(3, 'A', 1),(4, 'B', 2)
--------------
 
--------------
select * from produit
--------------
 
+----+-----------+-----------+
| id | reference | fk_langue |
+----+-----------+-----------+
|  3 | A         |         1 |
|  4 | B         |         2 |
+----+-----------+-----------+
--------------
CREATE TABLE IF NOT EXISTS `sous_produit_couleur` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txt` varchar(127) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `sous_produit_couleur` (`id`, `txt`) VALUES
(1, 'bleu'),
(2, 'rouge'),
(3, 'jaune'),
(4, 'vert')
--------------
 
--------------
select * from sous_produit_couleur
--------------
 
+----+-------+
| id | txt   |
+----+-------+
|  1 | bleu  |
|  2 | rouge |
|  3 | jaune |
|  4 | vert  |
+----+-------+
--------------
CREATE TABLE IF NOT EXISTS `sous_produit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `reference`  varchar(127) NOT NULL,
  `fk_couleur` int(11) NOT NULL,
  `fk_produit` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `sous_produit_fk_sous_produit_couleur` FOREIGN KEY (`fk_couleur`) REFERENCES `sous_produit_couleur` (`id`)  ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `sous_produit_fk_produit`              FOREIGN KEY (`fk_produit`) REFERENCES `produit` (`id`)               ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `sous_produit` (`id`, `reference`, `fk_couleur`, `fk_produit`) VALUES
(1, '1', 1, 3),
(2, '2', 1, 3),
(3, '3', 1, 3),
(4, '4', 2, 3),
(5, '5', 3, 3),
(6, '6', 3, 3),
(7, '7', 3, 3),
(8, '8', 2, 4),
(9, '9', 3, 4),
(10, '10', 3, 4)
--------------
 
--------------
select * from sous_produit
--------------
 
+----+-----------+------------+------------+
| id | reference | fk_couleur | fk_produit |
+----+-----------+------------+------------+
|  1 | 1         |          1 |          3 |
|  2 | 2         |          1 |          3 |
|  3 | 3         |          1 |          3 |
|  4 | 4         |          2 |          3 |
|  5 | 5         |          3 |          3 |
|  6 | 6         |          3 |          3 |
|  7 | 7         |          3 |          3 |
|  8 | 8         |          2 |          4 |
|  9 | 9         |          3 |          4 |
| 10 | 10        |          3 |          4 |
+----+-----------+------------+------------+
--------------
select *
 
from sous_produit
 
left outer join sous_produit_couleur
on sous_produit_couleur.id = sous_produit.fk_couleur
 
left outer join produit
on produit.id = sous_produit.fk_produit
 
left outer join produit_langue
on produit_langue.id = produit.fk_langue
--------------
 
+----+-----------+------------+------------+------+-------+------+-----------+-----------+------+------+
| id | reference | fk_couleur | fk_produit | id   | txt   | id   | reference | fk_langue | id   | txt  |
+----+-----------+------------+------------+------+-------+------+-----------+-----------+------+------+
|  1 | 1         |          1 |          3 |    1 | bleu  |    3 | A         |         1 |    1 | en   |
|  2 | 2         |          1 |          3 |    1 | bleu  |    3 | A         |         1 |    1 | en   |
|  3 | 3         |          1 |          3 |    1 | bleu  |    3 | A         |         1 |    1 | en   |
|  4 | 4         |          2 |          3 |    2 | rouge |    3 | A         |         1 |    1 | en   |
|  5 | 5         |          3 |          3 |    3 | jaune |    3 | A         |         1 |    1 | en   |
|  6 | 6         |          3 |          3 |    3 | jaune |    3 | A         |         1 |    1 | en   |
|  7 | 7         |          3 |          3 |    3 | jaune |    3 | A         |         1 |    1 | en   |
|  8 | 8         |          2 |          4 |    2 | rouge |    4 | B         |         2 |    2 | fr   |
|  9 | 9         |          3 |          4 |    3 | jaune |    4 | B         |         2 |    2 | fr   |
| 10 | 10        |          3 |          4 |    3 | jaune |    4 | B         |         2 |    2 | fr   |
+----+-----------+------------+------------+------+-------+------+-----------+-----------+------+------+
--------------
SELECT CONCAT(produit_langue.txt,' ',sous_produit_couleur.txt) AS 'categorie',
       COUNT(sous_produit.id)                                  AS 'compte'
 
from sous_produit
 
left outer join sous_produit_couleur
on sous_produit_couleur.id = sous_produit.fk_couleur
 
left outer join produit
on produit.id = sous_produit.fk_produit
 
left outer join produit_langue
on produit_langue.id = produit.fk_langue
 
group by categorie
--------------
 
+-----------+--------+
| categorie | compte |
+-----------+--------+
| en bleu   |      3 |
| en jaune  |      3 |
| en rouge  |      1 |
| fr jaune  |      2 |
| fr rouge  |      1 |
+-----------+--------+
--------------
SELECT CONCAT(produit_langue.txt,' ',sous_produit_couleur.txt) AS 'categorie',
       COUNT(sous_produit.id)                                  AS 'compte'
 
FROM            produit_langue
LEFT OUTER JOIN produit
ON produit.fk_langue = produit_langue.id
 
INNER JOIN sous_produit
ON sous_produit.fk_produit = produit.id
 
RIGHT JOIN sous_produit_couleur
ON sous_produit_couleur.id = sous_produit.fk_couleur
 
GROUP BY categorie
--------------
 
+-----------+--------+
| categorie | compte |
+-----------+--------+
| NULL      |      0 |
| en bleu   |      3 |
| en jaune  |      3 |
| en rouge  |      1 |
| fr jaune  |      2 |
| fr rouge  |      1 |
+-----------+--------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

Merci pour vos réponses,

Concernant la configuration, j'avais exporter via phpmyadmin ma base :s

Concernant la requête, je me suis mal exprimé. Ce qu'il faudrait à la fin serait quelque chose comme :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 en bleu 3 en rouge 1 en jaune 3 en vert 0 fr bleu 0 fr rouge 1 fr jaune 2 fr vert 0 de bleu 0 de rouge 0 de jaune 0 de vert 0 es bleu 0 es rouge 0 es jaune 0 es vert 0
Toutes les combinaisons possible de la jointure des tables couleur et langues avec le compte associé de sous produits en face (on ne veut pas compter les produits)

Et il n'est a priori pas possible trouver des sous produits sans couleur ou sans langue.

Salut Mathspountz.

Ta deuxième demande est différente de la première.
J'ai utilisé l'union, pour obtenir le tableau que tu désires.

La première partie n'est qu'un produit cartésien entre la table "produit_langue" et "sous_produit_couleur".
Je ne comptabilise par les tuples, donc je mets zéro.

La deuxième partie, c'est la requête précédente.

L'union des deux parties produit un résultat que je regroupe en faisant un sum(compte).

J'espère que c'est ce que tu désirais.

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
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
--------------
select categorie, sum(compte) as 'compte'
from (
    SELECT CONCAT(produit_langue.txt,' ',sous_produit_couleur.txt) AS 'categorie',
           0                                                       AS 'compte'
 
    from produit_langue, sous_produit_couleur
 
    union
 
    SELECT CONCAT(produit_langue.txt,' ',sous_produit_couleur.txt) AS 'categorie',
           COUNT(sous_produit.id)                                  AS 'compte'
 
    from sous_produit
 
    left outer join sous_produit_couleur
    on sous_produit_couleur.id = sous_produit.fk_couleur
 
    left outer join produit
    on produit.id = sous_produit.fk_produit
 
    left outer join produit_langue
    on produit_langue.id = produit.fk_langue
    group by categorie
) as x
group by categorie
order by categorie
--------------
 
+-----------+--------+
| categorie | compte |
+-----------+--------+
| de bleu   |      0 |
| de jaune  |      0 |
| de rouge  |      0 |
| de vert   |      0 |
| en bleu   |      3 |
| en jaune  |      3 |
| en rouge  |      1 |
| en vert   |      0 |
| es bleu   |      0 |
| es jaune  |      0 |
| es rouge  |      0 |
| es vert   |      0 |
| fr bleu   |      0 |
| fr jaune  |      2 |
| fr rouge  |      1 |
| fr vert   |      0 |
+-----------+--------+
 
Appuyez sur une touche pour continuer...

04/02/2016, 19h33
mathspountz

Merci ça fonctionne !

Je crois que je comprends comment ça marche et que je saurais le refaire, cet astucieux merci encore !