Discussion :

[Paul TOTH]

Bonjour,

j'ai conçu il y a quelques temps une application PHP/MySQL pour laquelle on me demandait de pouvoir attacher jusque 3 intervenants à une formation...pour ce genre de choses je ne prend pas de risque, et j'ai donc créé 3 tables : formations, intervenants, et intervenantsformation qui fait le lien entre les deux premières, c'est uniquement l'UI qui limite la saisie à 3 (triés par ordre alpha).

sauf que maintenant un intervenant extérieur voudrait extraire les données et réclame les 3 intervenants en colonnes

formation, intervenant1, intervenant2, intervenant3

du coup, est il possible, même avec une requête alambiquée de ressortir les trois premiers intervenants en colonnes ? (il peut y en avoir 0, 1, 2 ou 3...l'UI ne permet pas d'en avoir plus).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
formations:  
  id auto inc
  ...
intervenant
  id auto inc
  ...
intervenantsFormation
  formation INTEGER
  intervenant INTEGER
  ...

Merci

[Paul TOTH]

oh punaise ! j'ai trouvé un truc assez sioux

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT 
  f.libelle,
  i1.*,
  i2.*,
  i3.*
FROM formations f
LEFT JOIN intervenants i1 ON i1.id = (SELECT MIN(intervenant) FROM intervenantsFormation WHERE formation = f.id)
LEFT JOIN intervenants i3 ON i3.id <> i1.id AND i3.id = (SELECT MAX(intervenant) FROM intervenantsFormation WHERE formation = f.id)
LEFT JOIN intervenants i2 ON i2.id <> i1.id AND i2.id <> i3.id AND i2.id in (SELECT intervenant FROM intervenantsFormation WHERE formation = f.id)
where f.id = 6015

le seul bemol c'est que si je n'ai que 2 intervenants ils sont en i1 et i3 mais c'est déjà pas mal

EDIT: en fait il suffit d'inverser i2 et i3 dans la requête puisque de toute façon l'ordre des intervenants n'est pas ici décisif.

[Artemus24]

Salut Paul Toth.

C'est du grand n'importe quoi ta solution.
Et si tu passes à cinq intervenants, comment fais-tu ???

voici une solution qui répond à ton besoin :

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
 
--------------
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 `formation`
--------------
 
--------------
CREATE TABLE `formation`
( `id`         integer unsigned NOT NULL auto_increment primary key,
  `libelle`    varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `formation` (`libelle`) values
  ('un'),('deux'),('trois'),('quatre'),('cinq')
--------------
 
--------------
select * from formation
--------------
 
+----+---------+
| id | libelle |
+----+---------+
|  1 | un      |
|  2 | deux    |
|  3 | trois   |
|  4 | quatre  |
|  5 | cinq    |
+----+---------+
--------------
DROP TABLE IF EXISTS `intervenant`
--------------
 
--------------
CREATE TABLE `intervenant`
( `id`           integer unsigned NOT NULL auto_increment primary key,
  `id_formation` integer unsigned NOT NULL,
  `nom`          varchar(255)     NOT NULL,
  CONSTRAINT `FK_FORMATION` FOREIGN KEY (`id_formation`) REFERENCES `formation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `intervenant` (`id_formation`,`nom`) values
  (1,'andré'),(1,'pierre'),(2,'marcel'),(2,'paul'),(2,'jacques'),(3,'léon'),(3,'pierre'),(4,'françois'),(4,'thierry'),(5,'eric')
--------------
 
--------------
select * from intervenant
--------------
 
+----+--------------+----------+
| id | id_formation | nom      |
+----+--------------+----------+
|  1 |            1 | andré    |
|  2 |            1 | pierre   |
|  3 |            2 | marcel   |
|  4 |            2 | paul     |
|  5 |            2 | jacques  |
|  6 |            3 | léon     |
|  7 |            3 | pierre   |
|  8 |            4 | françois |
|  9 |            4 | thierry  |
| 10 |            5 | eric     |
+----+--------------+----------+
--------------
select id,
       libelle,
       max(nom1) as nom1,
       max(nom2) as nom2,
       max(nom3) as nom3
from (
    select id,
           libelle,
           case rang when 1 then nom else '.' end as nom1,
           case rang when 2 then nom else '.' end as nom2,
           case rang when 3 then nom else '.' end as nom3
    from (
        select f.id,
               f.libelle,
                 count(case when i.nom > j.nom then 1 else null end) + 1 as rang,
               i.nom
 
        from            formation    as f
 
        left outer join intervenant  as i
        on i.id_formation = f.id
 
        left outer join intervenant  as j
        on j.id_formation = f.id
 
        group by f.id, f.libelle, i.nom
        order by f.id, f.libelle, i.nom
    ) as x
) as y
group by id,libelle
--------------
 
+----+---------+----------+---------+------+
| id | libelle | nom1     | nom2    | nom3 |
+----+---------+----------+---------+------+
|  1 | un      | andré    | pierre  | .    |
|  2 | deux    | jacques  | marcel  | paul |
|  3 | trois   | léon     | pierre  | .    |
|  4 | quatre  | françois | thierry | .    |
|  5 | cinq    | eric     | .       | .    |
+----+---------+----------+---------+------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

[Paul TOTH]

Bonjour Aremus24,

merci pour tes encouragements mais tu n'as pas compris la structure de ma base

un même intervenant peut très bien intervenir sur plusieurs formations...d'où la présence d'une troisième table qui lie les deux premières...quand au nombre d'intervenants, c'est bien pour permettre d'en avoir 5 que ma base a cette structure et non celle, en ligne, réclamée par l'autre intervenant

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
formations:  
  id auto inc
  ...
intervenant
  id auto inc
  ...
intervenantsFormation
  formation INTEGER
  intervenant INTEGER
  ...

de plus, le nom de l'intervenant ne me suffit pas, il y a d'autres données dont j'ai besoin, d'où mon select, qui aurait même du être sous la forme plus générale

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT 
  f.*,
  i1.*,
  i2.*,
  i3.*

je regarde si je parviens à adapter ta requête à ma structure mais c'est chaud ...

alors ça donnerait cela :

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
 
select 
  f.*,
  i1.*,
  i2.*,
  i3.*
from (select x.id,
       case rang when 1 then x.intervenant else null end as i1,
       case rang when 2 then x.intervenant else null end as i2,
       case rang when 3 then x.intervenant else null end as i3
    from (
        select f.id,
               count(case when i.intervenant > j.intervenant then 1 else null end) + 1 as rang,
               i.intervenant
 
        from            formations    as f
 
        left outer join intervenantsFormation  as i
        on i.formation = f.id
 
        left outer join intervenantsFormation  as j
        on j.formation = f.id
 
        group by f.id, i.intervenant
        order by f.id, i.intervenant
    ) as x
) as z
join formations f on f.id = z.id
left join intervenants i1 on i1.id = z.i1
left join intervenants i2 on i1.id = z.i2
left join intervenants i3 on i1.id = z.i3
where z.id = 6015

( 1 total, Traitement en 0.0107 sec)

ma requête pour le même résultat ( 1 total, Traitement en 0.0000 sec)

avec un 'LIMIT 100' sans critère de sélection elle passe à 0.0100 sec, la tienne (enfin ma version ci-dessus) prend 2.5787 sec avec le LIMIT à la fin et 0.1001 sec avec le limit sur X

Pour le coup, à moins que je n'ai fait une autre bêtise d'après toi, je garde ma solution

Merci

[escartefigue]

Bonjour,

Et pourquoi pas quelque chose de ce genre (à tester, je n'ai pas créé le jeu de tables)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT for.id
     , for.libelle
     , GROUP_CONCAT(DISTINCT int_id ORDER BY int_id) AS liste_intervenants
FROM       formation    as for
INNER JOIN intervenants as int
   on int.formation_id = for.id
GROUP BY for.id
ORDER BY for.id
;

[aieeeuuuuu]

Bonjour,

Le mieux serait de pivoter dans l'application cliente si possible.
Sinon, est-ce qu'un GROUP_CONCAT ne ferait pas l'affaire ?

sinon (et enfin) je vous propose une variante de votre solution à tester :

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
 
SELECT 
  tmp.libelle,
  i1.*,
  i2.*,
  i3.*
FROM ( 
	SELECT 
			f.id
		,	f.libelle
		,	MIN(intervenant) as id1
		,	NULLIF(MAX(intervenant), MIN(intervenant)) as id2
	FROM	formations f
	LEFT JOIN intervenantsFormation i 
		ON i.id = f.formation
	where f.id = 6015
	GROUP BY f.id,	f.libelle
 
) tmp
LEFT JOIN intervenants i1 ON i1.id = tmp.id1
LEFT JOIN intervenants i3 ON i3.id = tmp.id2
LEFT JOIN intervenantsFormation der
		INNER JOIN intervenants i2 
			ON i2.id = der.intervenant
		ON der.formation = f.id
		AND i2.intervenant <> tmp.id1 
		AND i2.intervenant <> tmp.id3

[Artemus24]

Salut Paul TOTH.

Ma réaction s'est porté sur ce que vous avez mis ci-après :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
LEFT JOIN intervenants i1 ON i1.id =                                        (SELECT MIN(intervenant) FROM intervenantsFormation WHERE formation = f.id)
LEFT JOIN intervenants i3 ON i3.id <> i1.id AND i3.id =                     (SELECT MAX(intervenant) FROM intervenantsFormation WHERE formation = f.id)
LEFT JOIN intervenants i2 ON i2.id <> i1.id AND i2.id <> i3.id AND i2.id in (SELECT     intervenant  FROM intervenantsFormation WHERE formation = f.id)

Vous imposer des sous-requêtes, juste pour obtenir un ordre dans vos jointures !
De plus, le test "i3.id <> id.id" ne fonctionne pas si justement id.id est NULL. Le résultat sera toujours faux.
Je rappelle que NULL n'est pas une valeur mais un marqueur.

L'exemple que je vous ai donné, c'est une présentation classique en colonne.
Dans un premier temps, on prépare les lignes en les numérotants, d'où le rôle de la nouvelle colonne "rang".
Dans un second temps, on place en colonne les colonnes à partir du rang.
Dans un troisième temps, on fusionne les lignes ayant le même critère !

Sinon expliquez moi, comment basculer d'une présentation en ligne vers une présentation en colonne, sans procéder comme je l'ai fait ?

merci pour tes encouragements mais tu n'as pas compris la structure de ma base

J'ai bien compris le rôle joué par la table association (ta troisième table), mais ce n'est pas elle qui pose problème dans ta demande.
Pour obtenir ce que vous demandez, il est nécessaire de faire remonter sur la même ligne les colonnes des différentes jointures.

de plus, le nom de l'intervenant ne me suffit pas, il y a d'autres données dont j'ai besoin, d'où mon select, qui aurait même dû être sous la forme plus générale

Je ne voie pas ce qui peut poser problème dans mon exemple.
Vous ajoutez toutes les colonnes dont vous avez besoin pour votre traitement final. Cela va juste alourdir le temps d’exécution.

Pour le coup, à moins que je n'ai fait une autre bêtise d'après toi, je garde ma solution

Je suis désolé pour vous, mais votre solution ne fonctionne pas et ce, à cause d'une mauvaise gestion des NULL.

J'ai repris ma précédente solution, mais cette fois-ci avec une table association.
Et j'ai ajouté la solution que demande Paul Toth, pourvoir faire un select i.* sur les colonnes extraitent des jointures.

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
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
--------------
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 `formation`
--------------
 
--------------
CREATE TABLE `formation`
( `id`         integer unsigned NOT NULL auto_increment primary key,
  `libelle`    varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `formation` (`libelle`) values
  ('un'),('deux'),('trois'),('quatre'),('cinq'),('six'),('sept')
--------------
 
--------------
select * from formation
--------------
 
+----+---------+
| id | libelle |
+----+---------+
|  1 | un      |
|  2 | deux    |
|  3 | trois   |
|  4 | quatre  |
|  5 | cinq    |
|  6 | six     |
|  7 | sept    |
+----+---------+
--------------
DROP TABLE IF EXISTS `intervenant`
--------------
 
--------------
CREATE TABLE `intervenant`
( `id`           integer unsigned NOT NULL auto_increment primary key,
  `nom`          varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `intervenant` (`nom`) values
  ('andré'),('pierre'),('marcel'),('paul'),('jacques'),('léon'),('pierre'),('françois'),('thierry'),('eric')
--------------
 
--------------
select * from intervenant
--------------
 
+----+----------+
| id | nom      |
+----+----------+
|  1 | andré    |
|  2 | pierre   |
|  3 | marcel   |
|  4 | paul     |
|  5 | jacques  |
|  6 | léon     |
|  7 | pierre   |
|  8 | françois |
|  9 | thierry  |
| 10 | eric     |
+----+----------+
--------------
DROP TABLE IF EXISTS `association`
--------------
 
--------------
CREATE TABLE `association`
( `id`             integer unsigned NOT NULL auto_increment primary key,
  `id_formation`   integer unsigned NOT NULL,
  `id_intervenant` integer unsigned NOT NULL,
  CONSTRAINT `FK_FORMATION`   FOREIGN KEY (`id_formation`)   REFERENCES `formation`   (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_INTERVENANT` FOREIGN KEY (`id_intervenant`) REFERENCES `intervenant` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `association` (`id_formation`,`id_intervenant`) values
  (1, 1),(1, 2),(1, 3),
  (2, 2),(2, 4),
  (3, 5),
  (4, 3),(4, 6),(4, 7),
  (5, 1),(5, 3),(5, 8),
  (6, 6),(6, 7),
  (7, 9),(7,10)
--------------
 
--------------
select * from association
--------------
 
+----+--------------+----------------+
| id | id_formation | id_intervenant |
+----+--------------+----------------+
|  1 |            1 |              1 |
|  2 |            1 |              2 |
|  3 |            1 |              3 |
|  4 |            2 |              2 |
|  5 |            2 |              4 |
|  6 |            3 |              5 |
|  7 |            4 |              3 |
|  8 |            4 |              6 |
|  9 |            4 |              7 |
| 10 |            5 |              1 |
| 11 |            5 |              3 |
| 12 |            5 |              8 |
| 13 |            6 |              6 |
| 14 |            6 |              7 |
| 15 |            7 |              9 |
| 16 |            7 |             10 |
+----+--------------+----------------+
--------------
select id,
       libelle,
       max(nom1) as nom1,
       max(nom2) as nom2,
       max(nom3) as nom3
from (
    select id,
           libelle,
           case rang when 1 then x.nom else '.' end as nom1,
           case rang when 2 then x.nom else '.' end as nom2,
           case rang when 3 then x.nom else '.' end as nom3
    from (
        select f.id,
               f.libelle,
                 count(case when i.nom > j.nom then 1 else null end) + 1 as rang,
               i.nom
 
        from            association  as a
 
        inner join      formation    as f
        on f.id = a.id_formation
 
        left outer join intervenant  as i
        on i.id = a.id_intervenant
 
        inner join      association  as b
        on b.id_formation = a.id_formation
 
        left outer join intervenant  as j
        on j.id = b.id_intervenant
 
        group by f.id, f.libelle, i.nom
        order by f.id, f.libelle, i.nom
    ) as x
) as y
group by id,libelle
--------------
 
+----+---------+---------+----------+--------+
| id | libelle | nom1    | nom2     | nom3   |
+----+---------+---------+----------+--------+
|  1 | un      | andré   | marcel   | pierre |
|  2 | deux    | paul    | pierre   | .      |
|  3 | trois   | jacques | .        | .      |
|  4 | quatre  | léon    | marcel   | pierre |
|  5 | cinq    | andré   | françois | marcel |
|  6 | six     | léon    | pierre   | .      |
|  7 | sept    | eric    | thierry  | .      |
+----+---------+---------+----------+--------+
--------------
select f.*,i.*,j.*,k.*
from (select     id0,
             max(id1) as id1,
             max(id2) as id2,
             max(id3) as id3
      from (select id_formation                                       as id0,
                   case rang when 1 then id_intervenant else NULL end as id1,
                   case rang when 2 then id_intervenant else NULL end as id2,
                   case rang when 3 then id_intervenant else NULL end as id3
            from (select a.id_formation,
                           count(case when a.id_intervenant > b.id_intervenant then 1 else null end) + 1 as rang,
                         a.id_intervenant
 
                  from        association as a
                  inner join  association as b
                  on b.id_formation = a.id_formation
 
                  group by a.id_formation, a.id_intervenant
            ) as x
 
            group by id_formation, id_intervenant
      ) as y
 
      group by id0
) as z
 
inner      join formation    as f
on f.id = z.id0
 
left outer join intervenant  as i
on i.id = z.id1
 
left outer join intervenant  as j
on j.id = z.id2
 
left outer join intervenant  as k
on k.id = z.id3
--------------
 
+----+---------+------+---------+------+--------+------+----------+
| id | libelle | id   | nom     | id   | nom    | id   | nom      |
+----+---------+------+---------+------+--------+------+----------+
|  1 | un      |    1 | andré   |    2 | pierre |    3 | marcel   |
|  2 | deux    |    2 | pierre  |    4 | paul   | NULL | NULL     |
|  3 | trois   |    5 | jacques | NULL | NULL   | NULL | NULL     |
|  4 | quatre  |    3 | marcel  |    6 | léon   |    7 | pierre   |
|  5 | cinq    |    1 | andré   |    3 | marcel |    8 | françois |
|  6 | six     |    6 | léon    |    7 | pierre | NULL | NULL     |
|  7 | sept    |    9 | thierry |   10 | eric   | NULL | NULL     |
+----+---------+------+---------+------+--------+------+----------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

[Paul TOTH]

De plus, le test "i3.id <> id.id" ne fonctionne pas si justement id.id est NULL. Le résultat sera toujours faux.

si id.id est NULL, alors que c'est le MIN(id), c'est qu'il n'existe aucune correspondance et donc aucun intervenant.

[escartefigue]

Avez vous testé ma proposition basée sur GROUP_CONCAT post n° 5 ?

[Artemus24]

Salut paul Toth.

si id.id est NULL, alors que c'est le MIN(id), c'est qu'il n'existe aucune correspondance et donc aucun intervenant.

Et donc, la ligne sort quand même car vous avez mis un "left outer join".

@+

[Paul TOTH]

Avez vous testé ma proposition basée sur GROUP_CONCAT post n° 5 ?

Bonjour,

J'obtient les ID concaténés mais comment récupérer les informations des intervenants par colonnes ?

Salut paul Toth.


Et donc, la ligne sort quand même car vous avez mis un "left outer join".

@+

je sais bien que j'ai mis un left outer join, je souhaite avoir toutes les formations et pour chacune d'elle les 0, 1, 2 ou 3 intervenants, c'est indiqué dans mon premier message

du coup, est il possible, même avec une requête alambiquée de ressortir les trois premiers intervenants en colonnes ? (il peut y en avoir 0, 1, 2 ou 3...l'UI ne permet pas d'en avoir plus).

et si je voulais n'avoir que les formations avec intervenant, il me suffisait de remplacer le premier left join par un join puisqu'il est basé sur MIN(id)...

que ma requête ne soit pas dans vos standard de conception, je veux bien le croire, je ne suis pas spécialiste SQL et n'ai aucune prétention en la matière, mais indiquez moi en quoi cette requête est erronée ?!


toute l'histoire se résume à cela: le CdC demandait de pouvoir attacher jusque 3 intervenants à une formation, comme je déteste créer trois champs Intervenant avec le risque de devoir en ajouter d'autres par la suite, je préfère le modèle standard qui permet d'en attacher autant que l'on veux et simplement limiter la saisie dans l'interface utilisateur...

l'autre intervenant réclame les trois intervenants de chaque formation en ligne, au lieu de faire régresser ma base de données en réinjectant les intervenants dans la formation, j'ai cherché à produire une requête SQL qui me donne le même résultat qu'un select sur cette table:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
formations
  id
  libelle
  ..
  intervenant1
  intervenant2
  intervenant3
 
SELECT f.*, i1.*, i2.*, i3.* FROM
 formations f
LEFT JOIN intervenants i1 ON i1.id = f.intervenant1
LEFT JOIN intervenants i2 ON i2.id = f.intervenant2
LEFT JOIN intervenants i3 ON i3.id = f.intervenant3

il me semble que la requête que j'ai posté en réponse à ma question initiale est valide: grâce à MIN(id) je récupère le premier intervenant, MAX(id) <> i1 me donne le second, et le troisième ne doit être ni le premier, ni le dernier...chaque valeur pouvant être NULL

je l'ai testée sur une formation en particulier sur laquelle j'ai mis 0, 1, 2 puis 3 intervenants et elle fonctionne dans les 4 cas

et il se trouve qu'elle est plus performante que votre proposition....sommes nous d'accord là dessus ?

[escartefigue]

Bonjour,

J'obtient les ID concaténés mais comment récupérer les informations des intervenants par colonnes ?

Ajoutez les colonnes que vous souhaitez dans la concaténation
Cf. http://dev.mysql.com/doc/refman/5.7/...n_group-concat

[Artemus24]

Salut à tous.

En partant de la solution proposé par Escartefigue (désolé aieeeuuuuu, il a été le premier à proposer cela), je suis partie du résultat "group_concat".

Le but était de rechercher une solution, sans avoir trop de sous-requêtes, et de ne pas utiliser les fonctions "min()" et "max()" pour ordonner les résultats.
J'ai quand même une sous-requête, qui justement va produire le "group_concat() que l'on va exploiter par le suite dans les jointures.

Pour ce faire, je suis parti de la table association où j'ai récupéré d'une part la colonne "id_formation" et où j'ai concaténé la colonne "id_intervenant".
Si une restriction doit se faire, genre "where id_formation = 6015", c'est dans cette sous-requête qu'il faudra la mettre.

En ce qui concerne les jointures sur la table "intervenant", pour exploiter la colonne "liste", il faut pour ce faire utiliser la fonction "find_in_set" que je ne connaissais pas (j'ai jamais eu besoin de cette fonction jusqu'à présent).
Cette fonction manipule le type "set", sauf qu'ici, il n'y a pas de type "set" dans les tables (c'est pareil que "enum" pour ceux qui ne connaissent pas).

Voici le 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
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
--------------
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 `formation`
--------------
 
--------------
CREATE TABLE `formation`
( `id`         integer unsigned NOT NULL auto_increment primary key,
  `libelle`    varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `formation` (`libelle`) values
  ('un'),('deux'),('trois'),('quatre'),('cinq'),('six'),('sept')
--------------
 
--------------
select * from formation
--------------
 
+----+---------+
| id | libelle |
+----+---------+
|  1 | un      |
|  2 | deux    |
|  3 | trois   |
|  4 | quatre  |
|  5 | cinq    |
|  6 | six     |
|  7 | sept    |
+----+---------+
--------------
DROP TABLE IF EXISTS `intervenant`
--------------
 
--------------
CREATE TABLE `intervenant`
( `id`           integer unsigned NOT NULL auto_increment primary key,
  `nom`          varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `intervenant` (`nom`) values
  ('andré'),('pierre'),('marcel'),('paul'),('jacques'),('léon'),('pierre'),('françois'),('thierry'),('eric')
--------------
 
--------------
select * from intervenant
--------------
 
+----+----------+
| id | nom      |
+----+----------+
|  1 | andré    |
|  2 | pierre   |
|  3 | marcel   |
|  4 | paul     |
|  5 | jacques  |
|  6 | léon     |
|  7 | pierre   |
|  8 | françois |
|  9 | thierry  |
| 10 | eric     |
+----+----------+
--------------
DROP TABLE IF EXISTS `association`
--------------
 
--------------
CREATE TABLE `association`
( `id`             integer unsigned NOT NULL auto_increment primary key,
  `id_formation`   integer unsigned NOT NULL,
  `id_intervenant` integer unsigned NOT NULL,
  CONSTRAINT `FK_FORMATION`   FOREIGN KEY (`id_formation`)   REFERENCES `formation`   (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_INTERVENANT` FOREIGN KEY (`id_intervenant`) REFERENCES `intervenant` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `association` (`id_formation`,`id_intervenant`) values
  (1, 1),(1, 2),(1, 3),
  (2, 2),(2, 4),
  (3, 5),
  (4, 3),(4, 6),(4, 7),
  (5, 1),(5, 3),(5, 8),
  (6, 6),(6, 7),
  (7, 9),(7,10)
--------------
 
--------------
select * from association
--------------
 
+----+--------------+----------------+
| id | id_formation | id_intervenant |
+----+--------------+----------------+
|  1 |            1 |              1 |
|  2 |            1 |              2 |
|  3 |            1 |              3 |
|  4 |            2 |              2 |
|  5 |            2 |              4 |
|  6 |            3 |              5 |
|  7 |            4 |              3 |
|  8 |            4 |              6 |
|  9 |            4 |              7 |
| 10 |            5 |              1 |
| 11 |            5 |              3 |
| 12 |            5 |              8 |
| 13 |            6 |              6 |
| 14 |            6 |              7 |
| 15 |            7 |              9 |
| 16 |            7 |             10 |
+----+--------------+----------------+
--------------
select x.*, f.*, i.*, j.*, k.*
 
from (
    select  id_formation,
            group_concat(id_intervenant order by id_intervenant separator ',') as liste
      from  association
  group by  id_formation)   as x
 
inner join      formation   as f
on f.id = x.id_formation
 
left outer join intervenant as i
on find_in_set(i.id, x.liste)>0
 
left outer join intervenant as j
on  find_in_set(j.id, x.liste)>0
and j.id > i.id
 
left outer join intervenant as k
on  find_in_set(k.id, x.liste)>0
and k.id > j.id
and k.id > i.id
 
group by x.id_formation
order by x.id_formation
--------------
 
+--------------+-------+----+---------+------+---------+------+--------+------+----------+
| id_formation | liste | id | libelle | id   | nom     | id   | nom    | id   | nom      |
+--------------+-------+----+---------+------+---------+------+--------+------+----------+
|            1 | 1,2,3 |  1 | un      |    1 | andré   |    2 | pierre |    3 | marcel   |
|            2 | 2,4   |  2 | deux    |    2 | pierre  |    4 | paul   | NULL | NULL     |
|            3 | 5     |  3 | trois   |    5 | jacques | NULL | NULL   | NULL | NULL     |
|            4 | 3,6,7 |  4 | quatre  |    3 | marcel  |    6 | léon   |    7 | pierre   |
|            5 | 1,3,8 |  5 | cinq    |    1 | andré   |    3 | marcel |    8 | françois |
|            6 | 6,7   |  6 | six     |    6 | léon    |    7 | pierre | NULL | NULL     |
|            7 | 9,10  |  7 | sept    |    9 | thierry |   10 | eric   | NULL | NULL     |
+--------------+-------+----+---------+------+---------+------+--------+------+----------+
--------------
explain
select x.*, f.*, i.*, j.*, k.*
 
from (
    select  id_formation,
            group_concat(id_intervenant order by id_intervenant separator ',') as liste
      from  association
  group by  id_formation)   as x
 
inner join      formation   as f
on f.id = x.id_formation
 
left outer join intervenant as i
on find_in_set(i.id, x.liste)>0
 
left outer join intervenant as j
on  find_in_set(j.id, x.liste)>0
and j.id > i.id
 
left outer join intervenant as k
on  find_in_set(k.id, x.liste)>0
and k.id > j.id
and k.id > i.id
 
group by x.id_formation
order by x.id_formation
--------------
 
+----+-------------+-------------+-------+---------------+--------------+---------+-----------+------+----------------------------------------------------+
| id | select_type | table       | type  | possible_keys | key          | key_len | ref       | rows | Extra                                              |
+----+-------------+-------------+-------+---------------+--------------+---------+-----------+------+----------------------------------------------------+
|  1 | PRIMARY     | f           | ALL   | PRIMARY       | NULL         | NULL    | NULL      |    6 | Using temporary; Using filesort                    |
|  1 | PRIMARY     | <derived2>  | ref   | <auto_key0>   | <auto_key0>  | 4       | base.f.id |    2 | NULL                                               |
|  1 | PRIMARY     | i           | ALL   | NULL          | NULL         | NULL    | NULL      |   10 | Using where; Using join buffer (Block Nested Loop) |
|  1 | PRIMARY     | j           | ALL   | PRIMARY       | NULL         | NULL    | NULL      |   10 | Range checked for each record (index map: 0x1)     |
|  1 | PRIMARY     | k           | ALL   | PRIMARY       | NULL         | NULL    | NULL      |   10 | Range checked for each record (index map: 0x1)     |
|  2 | DERIVED     | association | index | FK_FORMATION  | FK_FORMATION | 4       | NULL      |   16 | NULL                                               |
+----+-------------+-------------+-------+---------------+--------------+---------+-----------+------+----------------------------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Personnellement, je tiens à souligner les points suivants :

1) on n'utilise pas mysql pour faire de la présentation de données.
Cela devient vite une usine à gaz, en terme de performance, mais aussi en maintenance de ce genre de requête.

2) vu que votre demande concernait une et une seule requête, il aurait été plus judicieux d'extraire les résultats de la sous-requête sur la table association et de les mettre, au préalable, dans une table temporaire.

3) je pense, sans trop me tromper, que vous avez un problème de modélisation de votre base de données.
On sait qu'il n'est pas propre de mettre dans une ligne, la répétition d'une même colonne, mais dans votre cas, on peut déroger à cette règle.

4) je ne dis pas que la table association est une erreur de conception, mais sans plus d'informations sur vos contraintes fonctionnelles, on ne peut prétendre que c'est une bonne solution.
Surtout, ce qui me choque, c'est en plein développement, remettre en cause une règle fonctionnelle !!!

5) l'astuce utilisée est contraire à la politique de mysql en ce qui concerne le type "set".
Bien que le type "set" ne soit pas utilisé dans cet exemple, on l'utilise d'une manière détournée.
A savoir, lier une colonne à un ensemble de valeur, créé par la fonction "group_concat()".

Instinctivement, on aurait dû utiliser la fonction "in()", mais le type "set" n'a pas une structure de tableau, mais celle d'un "varchar()".

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

on i.id in (x.liste)

C'est pourquoi, le fait d'avoir une virgule dans la chaîne de caractère, sélectionne que le premier élément dans le cas où la colonne à tester est numérique.
Dans le cas où la colonne est du type chaîne de caractères, c'est la totalité de la chaîne qui sera testée.

6) je n'ai pas testé en terme de performance, donc je ne peux pas répondre à votre attente.

7) je ne reviens pas sur mes commentaire de mon dernier message, car nous nous trouvons dans un autre contexte.
Mais il faut savoir que sur un "left outer join", il existe une grande différence de résultats entre un "on" et un "where".
Si vous avez besoin de tester l'équivalence entre deux colonnes, MySql a une particularité qui n'existe pas dans les autres SGBDR.
Il s'agit de l'opérateur "<=>" qui permet de tester si NULL = NULL.

8) j'ai produit un "explain" sur la requête afin de voir le résultat.
Vu que je n'ai pas optimisé les tables, vous aurez certainement un résultat différent.

Mais comme l'astuce est d'utiliser la fonction "find_in_set(), il faut savoir qu'il va balayer la totalité de la table et ne pas passer par les index.
--> http://dev.mysql.com/doc/refman/5.7/...on_find-in-set

@+