Salut à tous.
J'ai repris le lien suivant, donné par Escartefigue dans un autre sujet :
--> http://weblogs.sqlteam.com/jeffs/arc...joins-sql.aspx
Il s'agit d'un problème de jointure entre plusieurs tables.
J'ai repris les exercices que je retranscris ici, sous forme d'exécutable.
Dans ce premier exercice, l'auteur tient à nous souligner que le résultat qu'il attend est celui de la dernière requête que je reproduis ci-après :
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
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 -------------- SET AUTOCOMMIT = 0 -------------- -------------- START TRANSACTION -------------- -------------- DROP DATABASE IF EXISTS `base` -------------- -------------- CREATE DATABASE `base` DEFAULT CHARACTER SET `latin1` DEFAULT COLLATE `latin1_general_ci` -------------- -------------- DROP TABLE IF EXISTS `People` -------------- -------------- CREATE TABLE `People` ( `PersonID` integer unsigned not null auto_increment primary key, `PersonName` varchar(255) not null ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED -------------- -------------- insert into `People` (`PersonName`) values ('Fred Flintstone'), ('Barney Rubble'), ('George Jetson') -------------- -------------- select * from `People` -------------- +----------+-----------------+ | PersonID | PersonName | +----------+-----------------+ | 1 | Fred Flintstone | | 2 | Barney Rubble | | 3 | George Jetson | +----------+-----------------+ -------------- DROP TABLE IF EXISTS `PetTypes` -------------- -------------- CREATE TABLE `PetTypes` ( `PetTypeID` integer unsigned not null auto_increment primary key, `PetType` varchar(255) not null ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED -------------- -------------- insert into `PetTypes` (`PetType`) values ('Dinosaur'), ('Hopparoo') -------------- -------------- select * from `PetTypes` -------------- +-----------+----------+ | PetTypeID | PetType | +-----------+----------+ | 1 | Dinosaur | | 2 | Hopparoo | +-----------+----------+ -------------- DROP TABLE IF EXISTS `Pets` -------------- -------------- CREATE TABLE `Pets` ( `PetID` integer unsigned not null auto_increment primary key, `PetTypeID` integer unsigned not null, `PetName` varchar(255) not null, `OwnerID` integer unsigned not null, CONSTRAINT `FK_PETYPES` FOREIGN KEY (`PetTypeId`) REFERENCES `PetTypes` (`PetTypeID`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_PEOPLES` FOREIGN KEY (`OwnerID`) REFERENCES `People` (`PersonID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED -------------- -------------- insert into `Pets` (`PetTypeID`,`PetName`,`OwnerID`) values (1, 'Dino', 1), (2, 'Hoppy', 2) -------------- -------------- select * from `Pets` -------------- +-------+-----------+---------+---------+ | PetID | PetTypeID | PetName | OwnerID | +-------+-----------+---------+---------+ | 1 | 1 | Dino | 1 | | 2 | 2 | Hoppy | 2 | +-------+-----------+---------+---------+ -------------- select t1.PersonName, t2.PetName from People as t1 left outer join Pets as t2 on t2.OwnerID = t1.PersonID -------------- +-----------------+---------+ | PersonName | PetName | +-----------------+---------+ | Fred Flintstone | Dino | | Barney Rubble | Hoppy | | George Jetson | NULL | +-----------------+---------+ -------------- select t1.PersonName, t2.PetName, t3.PetType from People as t1 left outer join Pets as t2 on t2.OwnerID = t1.PersonID inner join PetTypes as t3 on t3.PetTypeID = t2.PetTypeID -------------- +-----------------+---------+----------+ | PersonName | PetName | PetType | +-----------------+---------+----------+ | Fred Flintstone | Dino | Dinosaur | | Barney Rubble | Hoppy | Hopparoo | +-----------------+---------+----------+ -------------- select t1.PersonName, t2.PetName, t3.PetType from People as t1 left outer join Pets as t2 on t2.OwnerID = t1.PersonID left outer join PetTypes as t3 on t3.PetTypeID = t2.PetTypeID -------------- +-----------------+---------+----------+ | PersonName | PetName | PetType | +-----------------+---------+----------+ | Fred Flintstone | Dino | Dinosaur | | Barney Rubble | Hoppy | Hopparoo | | George Jetson | NULL | NULL | +-----------------+---------+----------+ -------------- COMMIT -------------- -------------- SET AUTOCOMMIT = 1 -------------- Appuyez sur une touche pour continuer...
Dans le deuxième exercice, il introduit une nouvelle ligne dans la table "Pets", et cherche à obtenir le même résultat que précédemment.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7 +-----------------+---------+----------+ | PersonName | PetName | PetType | +-----------------+---------+----------+ | Fred Flintstone | Dino | Dinosaur | | Barney Rubble | Hoppy | Hopparoo | | George Jetson | NULL | NULL | +-----------------+---------+----------+
Comme par hasard, cette ligne nouvellement ajouté, n'est pas en relation avec la table "PetsTypes".
Comme on peut le voir dans la première requête, la ligne contenant le nom "George Jetson" n'apparait pas.
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
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 -------------- SET AUTOCOMMIT = 0 -------------- -------------- START TRANSACTION -------------- -------------- DROP DATABASE IF EXISTS `base` -------------- -------------- CREATE DATABASE `base` DEFAULT CHARACTER SET `latin1` DEFAULT COLLATE `latin1_general_ci` -------------- -------------- DROP TABLE IF EXISTS `People` -------------- -------------- CREATE TABLE `People` ( `PersonID` integer unsigned not null auto_increment primary key, `PersonName` varchar(255) not null ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED -------------- -------------- insert into `People` (`PersonName`) values ('Fred Flintstone'), ('Barney Rubble'), ('George Jetson') -------------- -------------- select * from `People` -------------- +----------+-----------------+ | PersonID | PersonName | +----------+-----------------+ | 1 | Fred Flintstone | | 2 | Barney Rubble | | 3 | George Jetson | +----------+-----------------+ -------------- DROP TABLE IF EXISTS `PetTypes` -------------- -------------- CREATE TABLE `PetTypes` ( `PetTypeID` integer unsigned not null auto_increment primary key, `PetType` varchar(255) not null ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED -------------- -------------- insert into `PetTypes` (`PetType`) values ('Dinosaur'), ('Hopparoo') -------------- -------------- select * from `PetTypes` -------------- +-----------+----------+ | PetTypeID | PetType | +-----------+----------+ | 1 | Dinosaur | | 2 | Hopparoo | +-----------+----------+ -------------- DROP TABLE IF EXISTS `Pets` -------------- -------------- CREATE TABLE `Pets` ( `PetID` integer unsigned not null auto_increment primary key, `PetTypeID` integer unsigned null, `PetName` varchar(255) not null, `OwnerID` integer unsigned not null, CONSTRAINT `FK_PETYPES` FOREIGN KEY (`PetTypeId`) REFERENCES `PetTypes` (`PetTypeID`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_PEOPLES` FOREIGN KEY (`OwnerID`) REFERENCES `People` (`PersonID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED -------------- -------------- insert into `Pets` (`PetTypeID`,`PetName`,`OwnerID`) values (1, 'Dino', 1), (2, 'Hoppy', 2), (null, 'Baby Puss', 1) -------------- -------------- select * from `Pets` -------------- +-------+-----------+-----------+---------+ | PetID | PetTypeID | PetName | OwnerID | +-------+-----------+-----------+---------+ | 1 | 1 | Dino | 1 | | 2 | 2 | Hoppy | 2 | | 3 | NULL | Baby Puss | 1 | +-------+-----------+-----------+---------+ -------------- select t1.PersonName, t2.PetName, t3.PetType from People as t1 left outer join Pets as t2 on t2.OwnerID = t1.PersonID inner join PetTypes as t3 on t3.PetTypeID = t2.PetTypeID -------------- +-----------------+---------+----------+ | PersonName | PetName | PetType | +-----------------+---------+----------+ | Fred Flintstone | Dino | Dinosaur | | Barney Rubble | Hoppy | Hopparoo | +-----------------+---------+----------+ -------------- select ta.ownerID, ta.PetName, tb.PetType from Pets as ta inner join PetTypes as tb on tb.PetTypeID = ta.PetTypeID -------------- +---------+---------+----------+ | ownerID | PetName | PetType | +---------+---------+----------+ | 1 | Dino | Dinosaur | | 2 | Hoppy | Hopparoo | +---------+---------+----------+ -------------- select t1.PersonName, t2.PetName, t2.PetType from People as t1 left outer join ( select ta.ownerID, ta.PetName, tb.PetType from Pets as ta inner join PetTypes as tb on tb.PetTypeID = ta.PetTypeID ) as t2 on t2.OwnerID = t1.PersonID -------------- +-----------------+---------+----------+ | PersonName | PetName | PetType | +-----------------+---------+----------+ | Fred Flintstone | Dino | Dinosaur | | Barney Rubble | Hoppy | Hopparoo | | George Jetson | NULL | NULL | +-----------------+---------+----------+ -------------- COMMIT -------------- -------------- SET AUTOCOMMIT = 1 -------------- Appuyez sur une touche pour continuer...
En fait, le problème vient dans la jointure où il est mis un "inner joint" (une jointure interne).
La solution proposée consiste à créer une premier extraction entre la table "Pets" et la table "PetTypes", sous la forme d'une sous-requête.
J'ai reproduis dans la deuxième requête, le contenu de ce résultat.
Puis dans la troisième requête de faire une jointure entre la table "people" et cette sous-requête.
Le tout afin de reproduire, à l'identique, la même extraction que dans le premier exercice.
Franchement, est-ce bien utile de procéder ainsi ?
Je propose la solution suivante, qui permet aussi de produire le même résultat :
Comme on peut le voir, il est inutile de passer par une sous-requête pour obtenir le même résultat.
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
34
35
36 -------------- set sql_mode = '' -------------- -------------- select t1.PersonName, t2.PetName, t3.PetType from People as t1 left outer join Pets as t2 on t2.OwnerID = t1.PersonID left outer join PetTypes as t3 on t3.PetTypeID = t2.PetTypeID group by t1.PersonName -------------- +-----------------+---------+----------+ | PersonName | PetName | PetType | +-----------------+---------+----------+ | Fred Flintstone | Dino | Dinosaur | | Barney Rubble | Hoppy | Hopparoo | | George Jetson | NULL | NULL | +-----------------+---------+----------+ -------------- COMMIT -------------- -------------- SET AUTOCOMMIT = 1 -------------- Appuyez sur une touche pour continuer...
Il y a juste un regroupement à faire sur le nom de la personne.
Si vous avez des commentaires à faire sur cet exercice, je suis preneur.
Merci !
@+
Partager