Une condition dans un SUM

**mioummioum** · 31/03/2015, 18h17

Bonjour,

Je cherche le moyen de définir "à la volée" la valeur d'une colonne dans un HAVING SUM. C'est possible ?

Grosso modo je voudrais pouvoir dire si toi (la colonne de la ligne 1) tu as la valeur "null" alors ta valeur est 1000 sinon tu as la valeur que tu as.
le having continue, ligne 2 -> 35
ligne 3 -> 4
ligne 4 -> de nouveau "null" donc 1000

pour au final me donnait -> 2039 (1000+35+4+1000)

mais je ne sais pas comment faire dans la syntaxe SQL. J'ai essayé de mettre une condition dans mon PHP (quand j'écris le having sum) mais ça ne semble pas marcher.

Pouvez-vous m'aider svp merci ?

M_M

**skuatamad** · 31/03/2015, 18h52

COALESCE

**mioummioum** · 01/04/2015, 16h40

Bonjour skuatamad,

Je ne connaissais pas ces fonctions, merci à toi pour le partage.

Par contre je ne suis pas certaine que ça corresponde à mon besoin, je dis bien je ne suis pas certaine.

Mon SELECT est assez grand et se termine par un group by clientele.id_client HAVING SUM(UNIX_TIMESTAMP(fin) - UNIX_TIMESTAMP(debut)) BETWEEN 10 AND 20

Je n'ai donc qu'une requête et l'idée c'est de qualifier tous les "fin" (de la colonne de ma table) qui sont à "null". Tu me suis ?

M_M

**CinePhil** · 03/04/2015, 12h02

Difficile à comprendre votre besoin !

Donnez-nous la structure de votre table et votre requête.

**mioummioum** · 14/04/2015, 10h37

Kikoo,

J'ai pas la requête sous la main mais je souhaite faire la somme de plusieurs différences entre 2 dates sur plusieurs lignes pour retourner un super TIMESTAMP.

Tant que je renseigne la date de début et la date de fin, pas de soucis car 4-2 me renvoie bien 2.

Seulement toutes les dates de fin ne sont pas renseignées (et doivent l'être à la volée => date du jour) donc imaginons que 4 = hier et 5 = aujourd'hui je fais donc 5-2 = 3 mais comme le champ est vide le résultat est 0-2 soit -2 au lieu de mon 3 donc le calcul est faux d'où mon idée de définir à la volée les valeurs 0 qui correspondent à la date du jour car je ne me vois pas faire un update de toute ma table à chaque requête.

Espérant avoir été claire

la table recidives (oui toto a fait de la prison

) peut se résumer à

id qui debut fin
1 toto 2015-02-01 2015-03-01
2 toto 2014-12-12 0000-00-00
3 tutu 2013-08-01 2010-05-06

et faire un

Code :

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

SELECT * FROM recidives order by id group by qui HAVING SUM(UNIX_TIMESTAMP(fin) - UNIX_TIMESTAMP(debut)) BETWEEN 1 AND 3

pour savoir si toto rentre dans le cas des gens qui ont fait entre 1 an et 3 ans de prison et si tutu est concerné (je craque et on est que mardi

)

C'est juste ce 0000-00-00 qui me saoule

Merci de votre aide.

M_M

**CinePhil** · 14/04/2015, 11h15

1) Il vaut mieux éviter la guerre des étoiles !

2) Toutes les colonnes du SELECT ne faisant pas l'objet d'une fonction de groupage doivent figurer dans le GROUP BY sous peine de voir des valeurs aléatoires pour les colonnes manquantes.

Pour calculer le nombre de jours entre deux dates, il faut utiliser DATEDIFF avec MySQL.

Combien de jours pour chaque ligne de la table ?

Code :

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

1
2
3
4
5
6
SELECT id, qui,
	CASE
		WHEN fin = '0000-00-00' OR fin IS NULL THEN DATEDIFF(CURRENT_DATE, debut)
		ELSE DATEDIFF(fin, debut)
	END AS nb_jours
FROM recidives

Combien de jours pour chaque prisonnier ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT qui, SUM(nb_jours) AS total_jours
FROM
(
	SELECT id, qui,
		CASE
			WHEN fin = '0000-00-00' OR fin IS NULL THEN DATEDIFF(CURRENT_DATE, debut)
			ELSE DATEDIFF(fin, debut)
		END AS nb_jours
	FROM recidives
) tmp
GROUP BY qui

Quels sont ceux qui ont fait entre un et trois ans de prison (on fera abstraction des années bissextiles) ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT qui, SUM(nb_jours) AS total_jours
FROM
(
	SELECT id, qui,
		CASE
			WHEN fin = '0000-00-00' OR fin IS NULL THEN DATEDIFF(CURRENT_DATE, debut)
			ELSE DATEDIFF(fin, debut)
		END AS nb_jours
	FROM recidives
) tmp
GROUP BY qui
HAVING total_jours BETWEEN 365 AND 1095

**mioummioum** · 22/04/2015, 11h47

Bonjour CinePhil et merci

Dans ma syntaxe en PHP je voudrais faire du coup la même chose (j'ai bien pris note de vos réserves et remarques) cependant j'aimerai déjà faire marcher ma requête dans l'état si je puis dire.

Grosso modo elle se présente de la sorte :

Code :

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

SELECT * FROM table1, table2, table3... WHERE table1... GROUP BY table1.id_table1 HAVING SUM(UNIX_TIMESTAMP(table3.fin_table3) - UNIX_TIMESTAMP(table3.debut_table3)) BETWEEN ...

En reprenant votre syntaxe j'ai essayé ceci :

Code :

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

1
2
$cinephil = date("Y-m-d");
SELECT *, CASE WHEN table3.fin_table3 = '0000-00-00' THEN table3.fin_table3 = $cinephil END FROM ...

le reste ne change pas.

mais ça ne semble pas marcher

J'aimerai vraiment faire fonctionner la requête dans ce premier état svp merci (même si c'est pas très propre) => c'est plus un mélange entre le challenge et l'urgence.

M_M

**mioummioum** · 24/08/2015, 13h53

Kikoo,

j'avais une dernière intervention à ce sujet. Dans le cas de jointures construites au gré de filtres, comment construire le select sans faire la fameuse guerre des étoiles ?
http://www.expreg.com/fred_article.p...re_des_etoiles

Si je scinde la table detenus de la table temps de prison.

Comment faire une jointure et afficher tout le monde ?

M_M

**CinePhil** · 24/08/2015, 17h25

Envoyé par mioummioum

(j'ai bien pris note de vos réserves et remarques)

Euh... pas vraiment, non !
Exemple :

Envoyé par mioummioum

Code :

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

SELECT *

Alors que j'avais écrit :

Envoyé par CinéPhil

1) Il vaut mieux éviter la guerre des étoiles !

Et avec ça :

Code :

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

FROM table1, table2, table3

J'ajouterai maintenant ceci :
Les jointures s'écrivent depuis 1992 avec l'opérateur JOIN ; il serait temps de s'y mettre !

Code PHP :

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

1
2
$cinephil = date("Y-m-d");
SELECT *, CASE WHEN table3.fin_table3 = '0000-00-00' THEN table3.fin_table3 = $cinephil END FROM ...

Pourquoi vous emmerder avec une fonction PHP pour récupérer la date du jour dans une variable alors qu'il existe CURRENT_DATE en SQL ?

Dans mon message #6, je vous avais donné la démarche progressive pour construire la requête dont vous aviez semble t-il besoin, en regard de ce que j'ai compris de votre besoin.

Pourquoi ne pas avoir simplement repris ou vous être inspiré de ma requête finale :

Envoyé par CinéPhil

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT qui, SUM(nb_jours) AS total_jours
FROM
(
	SELECT id, qui,
		CASE
			WHEN fin = '0000-00-00' OR fin IS NULL THEN DATEDIFF(CURRENT_DATE, debut)
			ELSE DATEDIFF(fin, debut)
		END AS nb_jours
	FROM recidives
) tmp
GROUP BY qui
HAVING total_jours BETWEEN 365 AND 1095

Envoyé par mioummioum

J'aimerai vraiment faire fonctionner la requête dans ce premier état svp merci (même si c'est pas très propre) => c'est plus un mélange entre le challenge et l'urgence.

Et ben en fait d'urgence, vous êtes semble t-il bloqué dessus depuis 4 mois ! Pas si urgente que ça, l'urgence !
En urgence, inspirez-vous de ma requête finale qui est bien meilleure que la votre.

Si vous l'adaptez et que votre adaptation ne fonctionne pas, postez votre requête adaptée de la mienne et on pourra analyser pourquoi elle ne fonctionne pas.

Dans le cas de jointures construites au gré de filtres, comment construire le select sans faire la fameuse guerre des étoiles ?

Pouvez-vous être plus précis sur votre besoin ?

Si je scinde la table detenus de la table temps de prison.

Comment faire une jointure et afficher tout le monde ?

Schématiquement avec les hypothétiques tables suivantes :
detenu (det_id, det_nom, det_prenom, det_matricule...)
detention (dtn_id, dtn_id_detenu, dtn_date_debut, dtn_duree_prevue...)

Donner tous les détenus et leurs détention :

Code :

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

1
2
3
4
5
SELECT d1.dte_id, d1.dte_matricule, d1.dte_nom, d1.dte_prenom
	d2.dtn_id, d2.dtn_date_debut, d2.dtn_duree_prevue
FROM detenu d1
INNER JOIN detention d2 ON d2.dtn_id_detenu = d1.dte_id
ORDER BY d1.dte, d2.dtn_date_debut

**Artemus24** · 24/08/2015, 19h45

Salut à tous.

J'ai répondu par deux exemples aux deux questions (message #1 et #5).

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
--------------
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 `test`
--------------
 
--------------
CREATE TABLE `test`
(
  `id`     integer unsigned  NOT NULL,
  `col`    integer unsigned      NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`id`,`col`) VALUES
(1, NULL), (2, 35), (3, 4), (4, NULL)
--------------
 
--------------
select * from `test`
--------------
 
+----+------+
| id | col  |
+----+------+
|  1 | NULL |
|  2 |   35 |
|  3 |    4 |
|  4 | NULL |
+----+------+
--------------
select id, sum(coalesce(col, 1000)) as total
from `test`
having total = 2039
--------------
 
+----+-------+
| id | total |
+----+-------+
|  1 |  2039 |
+----+-------+
--------------
DROP TABLE IF EXISTS `recidives`
--------------
 
--------------
CREATE TABLE `recidives`
(
  `id`     integer unsigned  NOT NULL,
  `qui`    char(4)           NOT NULL,
  `debut`  date                  NULL,
  `fin`    date                  NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `recidives` (`id`,`qui`,`debut`,`fin`) VALUES
(1, 'toto', '2015-02-01', '2015-03-01'),
(2, 'toto', '2014-12-12', NULL),
(3, 'tutu', '2010-05-06', '2013-08-01'),
(4, 'tata', NULL,         '2015-12-31')
--------------
 
--------------
select * from `recidives`
--------------
 
+----+------+------------+------------+
| id | qui  | debut      | fin        |
+----+------+------------+------------+
|  1 | toto | 2015-02-01 | 2015-03-01 |
|  2 | toto | 2014-12-12 | NULL       |
|  3 | tutu | 2010-05-06 | 2013-08-01 |
|  4 | tata | NULL       | 2015-12-31 |
+----+------+------------+------------+
--------------
SELECT id, qui, SUM(datediff(coalesce(fin,now()), coalesce(debut,now()))) as val
FROM recidives
group by id
order by id
--------------
 
+----+------+------+
| id | qui  | val  |
+----+------+------+
|  1 | toto |   28 |
|  2 | toto |  255 |
|  3 | tutu | 1183 |
|  4 | tata |  129 |
+----+------+------+
--------------
SELECT qui, SUM(datediff(coalesce(fin,now()), coalesce(debut,now()))) as val
FROM recidives
group by qui
order by qui
--------------
 
+------+------+
| qui  | val  |
+------+------+
| tata |  129 |
| toto |  283 |
| tutu | 1183 |
+------+------+
--------------
SELECT qui, SUM(datediff(coalesce(fin,now()), coalesce(debut,now()))) as val
FROM recidives
group by qui
having val between 365 and 1460
order by qui
--------------
 
+------+------+
| qui  | val  |
+------+------+
| tutu | 1183 |
+------+------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

@+

**mioummioum** · 24/08/2015, 22h09

Bonsoir,

Désolée, j'ai eu un souci de santé

L'introduction de mon message c'était justement comment construire le select sans faire la fameuse guerre des étoiles (l'autre post était du 22 avril)

A la base j'ai juste 2 tables liées et je voulais afficher pour chaque personne un résultat (0 pour les personnes qui n'ont pas de lignes dans la table récidives)

Si vous savez comment faire je suis preneuse car là je suis dans le flou à plus d'un titre...

M_M

**Artemus24** · 24/08/2015, 23h46

Salut mioummioum.

Envoyé par mioummioum

L'introduction de mon message c'était justement comment construire le select sans faire la fameuse guerre des étoiles

Vous ne devez jamais mettre un "select *" dans vos applications. A la place, vous devez nommer explicitement chaque colonne, en précisant l'alias de la table dans le cas ou vous faites une ou plusieurs jointures.

Code :

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

1
2
3
4
select tb1.col1, tb2.col2, tb1.col3, ...
from       ma_table_1 AS tb1
inner join ma_table_2 as tb2
on tb2.clef = tb1.clef

Envoyé par mioummioum

A la base j'ai juste 2 tables liées et je voulais afficher pour chaque personne un résultat (0 pour les personnes qui n'ont pas de lignes dans la table récidives)

Vous ne devez pas faire un 'inner join' car si dans la seconde table, pour une clef donnée, la ligne n'existe pas, alors la ligne de la première table ne s'affichera pas.
Vous devez utiliser un 'left outer join' à la place. Si dans la seconde table, la ligne n'existe pas, elle s'affichera avec que des 'null'.

Je ne voie pas trop où se trouve la difficulté dans votre problème.
Reprenez l'exemple que je vous ai donné pour le calcul de la différence entre deux dates.
Construisez étape par étape votre requête. Produisez d'abord la jointure, et ensuite le calcul de la différence.

Pour la suite de votre sujet, je préfère un jeu d'essai un peu plus réaliste.

@+

**mioummioum** · 25/08/2015, 10h18

Bonjour et merci.

La difficulté réside dans ce que je suis capable de faire au vue de ce qu'on peut faire et surtout de mes connaissances

J'ai fait ceci un peu au p'tit bonheur la chance je dois dire. Ca marche mais je voudrais comprendre pourquoi ?

Code :

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

1
2
3
4
5
6
SELECT recidives.fin, recidives.debut, detenus.nom, couleurs.libelle, couleurs.detenu, SUM(datediff(coalesce(recidives.fin,now()), coalesce(recidives.debut,now()))) as val
FROM recidives 
RIGHT OUTER JOIN detenus ON idd = qui
LEFT OUTER JOIN couleurs ON detenu = idd
group by detenus.nom
order by detenus.nom

J'avais mis LEFT OUTER JOIN partout mais ça ne marchait pas et là RIGHT OUTER JOIN => miracle ! Tant mieux mais pourquoi ?

J'ai également créé une table "couleurs" pour tester plusieurs jointures. Cette fois c'est LEFT...

Dans votre dernier message vous me demandiez de créer d'abord mes jointures puis mon calcul mais je ne suis pas arrivée à mieux que ça.

Si je peux avoir votre œil averti.

Merci

M_M

**CinePhil** · 25/08/2015, 11h06

La difficulté réside dans ce que je suis capable de faire au vue de ce qu'on peut faire et surtout de mes connaissances

Pour améliorer vos connaissances, j'ai donné les liens, dans mes messages, vers les cours de SQLPro sur les jointures et sur les groupages. Les avez-vous lus et compris ?

Apparemment non :

Code :

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

1
2
3
SELECT recidives.fin, recidives.debut, detenus.nom, couleurs.libelle, couleurs.detenu, SUM(datediff(coalesce(recidives.fin,now()), coalesce(recidives.debut,now()))) as val
-- ...
group by detenus.nom

Encore une fois, toutes les colonnes du SELECT ne faisant pas l'objet d'une fonction de groupage doivent figurer dans le GROUP BY sous peine de voir des valeurs aléatoires pour les colonnes manquantes.

Code :

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

1
2
3
FROM recidives 
RIGHT OUTER JOIN detenus ON idd = qui
LEFT OUTER JOIN couleurs ON detenu = idd

Je déduis de vos jointures que vous avez ce schéma de BDD :
detenu -0,n----effectuer----1,1- recidive
|-----------0,n----avoir----1,1- couleur

On peut comprendre qu'une récidive ne concerne qu'un seul détenu mais il est étonnant qu'une couleur ne soit affectée qu'à un seul détenu, non ?
D'ailleurs, que signifie ce concept de couleur affecté à un détenu ?

Comme vous n'avez pas mis d'alias dans votre requête, je crois comprendre que "idd" est l'identifiant du détenu, que "qui" est la clé étrangère faisant référence à l'identifiant du détenu dans la table recidives (au passage, vous auriez dû nommer vos tables au singulier) et que detenu est la clé étrangère faisant référence à l'identifiant du détenu dans la table couleurs.
Si vous aviez commencé vos jointures à partir de la table detenus, vous auriez pu utiliser les deux fois LEFT OUTER JOIN.

Que cherchez vous à obtenir par cette requête ?

**Artemus24** · 25/08/2015, 11h32

Salut mioummioum.

Envoyé par mioummioum

La difficulté réside dans ce que je suis capable de faire au vue de ce qu'on peut faire et surtout de mes connaissances

Cela va vous surprendre mais ma principale difficultée est plus dans la façon de formuler vos besoins que de leur résolution à proprement parler.
J'entends par là, essayer de comprendre ce que vous cherchez à faire, ou tentez de faire, puis ensuite d'envisager une solution.
Obtenir une première ébauche n'est que le premier pas vers ce que vous chercher à obtenir et non une finalité en soi.
Après on peut monter en puissance dans la complexité afin d'obtenir de bonnes performances.

Envoyé par mioummioum

J'ai fait ceci un peu au p'tit bonheur la chance je dois dire.

Avec un jeu d'essai, cela aurait été encore mieux. Quand on travaille sur un exemple, on voie tout de suite les problèmes.

Voici un exemple pour faire comprendre l'intérêt de la jointure et de ces variantes.

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
--------------
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 `pere`
--------------
 
--------------
CREATE TABLE `pere`
(
  `pere_id`  integer unsigned  NOT NULL,
  `libelle`  char(20)          NOT NULL,
  PRIMARY KEY (`pere_id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `pere` (`pere_id`,`libelle`) VALUES
(1, 'rouge'), (2, 'vert'), (3, 'jaune'), (4, 'bleu')
--------------
 
--------------
select * from `pere`
--------------
 
+---------+---------+
| pere_id | libelle |
+---------+---------+
|       1 | rouge   |
|       2 | vert    |
|       3 | jaune   |
|       4 | bleu    |
+---------+---------+
--------------
DROP TABLE IF EXISTS `fils`
--------------
 
--------------
CREATE TABLE `fils`
(
  `fils_id`  integer unsigned  NOT NULL,
  `pere_id`  integer unsigned  NOT NULL,
  `nuance`   char(20)          NOT NULL,
  PRIMARY KEY (`fils_id`),
  FOREIGN KEY (`pere_id`) REFERENCES `pere` (`pere_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `fils` (`fils_id`,`pere_id`,`nuance`) VALUES
(1, 1, 'carmin'), (2, 1, 'cinabre'), (3, 3, 'citron'), (4, 3, 'ambre'), (5, 3, 'moutarde')
--------------
 
--------------
select * from `fils`
--------------
 
+---------+---------+----------+
| fils_id | pere_id | nuance   |
+---------+---------+----------+
|       1 |       1 | carmin   |
|       2 |       1 | cinabre  |
|       3 |       3 | citron   |
|       4 |       3 | ambre    |
|       5 |       3 | moutarde |
+---------+---------+----------+
--------------
select *
from       `pere` as p
inner join `fils` as f
on f.pere_id = p.pere_id
--------------
 
+---------+---------+---------+---------+----------+
| pere_id | libelle | fils_id | pere_id | nuance   |
+---------+---------+---------+---------+----------+
|       1 | rouge   |       1 |       1 | carmin   |
|       1 | rouge   |       2 |       1 | cinabre  |
|       3 | jaune   |       3 |       3 | citron   |
|       3 | jaune   |       4 |       3 | ambre    |
|       3 | jaune   |       5 |       3 | moutarde |
+---------+---------+---------+---------+----------+
--------------
select *
from       `pere` as p
left outer join `fils` as f
on f.pere_id = p.pere_id
--------------
 
+---------+---------+---------+---------+----------+
| pere_id | libelle | fils_id | pere_id | nuance   |
+---------+---------+---------+---------+----------+
|       1 | rouge   |       1 |       1 | carmin   |
|       1 | rouge   |       2 |       1 | cinabre  |
|       3 | jaune   |       3 |       3 | citron   |
|       3 | jaune   |       4 |       3 | ambre    |
|       3 | jaune   |       5 |       3 | moutarde |
|       2 | vert    |    NULL |    NULL | NULL     |
|       4 | bleu    |    NULL |    NULL | NULL     |
+---------+---------+---------+---------+----------+
--------------
 
 
Appuyez sur une touche pour continuer...

Ici, vous voyez que dans la jointure de type 'inner', les lignes de la table 'père', entre autre pour les couleurs 'vert' et 'bleu', n'apparaissent pas, car il n'y a pas de correspondance dans la table fils.

Inversement, dans la jointure de type 'left outer', les couleurs 'vert' et 'bleu' apparaissent cette fois-ci. Mais comme il n'y a aucune correspondance dans la table fils, les lignes de la jointure sont toutes à 'null'.

Envoyé par mioummioum

J'avais mis LEFT OUTER JOIN partout mais ça ne marchait pas et là RIGHT OUTER JOIN => miracle ! Tant mieux mais pourquoi ?

Un 'left outer' ou un 'right outer', ça fait exactement la même chose, sauf que vous inversez l'ordre des tables quand vous écrivez votre jointure,
Je reprends mon exemple précédent, mais en inversant les tables père et fils et avec l'usage de 'left outer' et de 'right outer'.

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
select *
from            `fils` as f
left outer join `pere` as p
on p.pere_id = f.pere_id
--------------
 
+---------+---------+----------+---------+---------+
| fils_id | pere_id | nuance   | pere_id | libelle |
+---------+---------+----------+---------+---------+
|       1 |       1 | carmin   |       1 | rouge   |
|       2 |       1 | cinabre  |       1 | rouge   |
|       3 |       3 | citron   |       3 | jaune   |
|       4 |       3 | ambre    |       3 | jaune   |
|       5 |       3 | moutarde |       3 | jaune   |
+---------+---------+----------+---------+---------+
--------------
select *
from             `fils` as f
right outer join `pere` as p
on p.pere_id = f.pere_id
--------------
 
+---------+---------+----------+---------+---------+
| fils_id | pere_id | nuance   | pere_id | libelle |
+---------+---------+----------+---------+---------+
|       1 |       1 | carmin   |       1 | rouge   |
|       2 |       1 | cinabre  |       1 | rouge   |
|       3 |       3 | citron   |       3 | jaune   |
|       4 |       3 | ambre    |       3 | jaune   |
|       5 |       3 | moutarde |       3 | jaune   |
|    NULL |    NULL | NULL     |       2 | vert    |
|    NULL |    NULL | NULL     |       4 | bleu    |
+---------+---------+----------+---------+---------+

Si vous partez de la table 'père' et que vous chercher à lier vos lignes à celle de la table 'fils', vous n'obtiendrez pas le même résultat si vous inversez vos deux tables comme dans cet exemple.

C'est pourquoi j'ai nommé, dans mon message précédent, la première table 'maître' et la seconde table 'esclave'.
Car en partant de la table 'maitre', c'est elle qui décide ce que vous allez remonter comme résultat.
Vous devez comprendre que si une valeur de cette clef de la table 'maître' n'existe pas, elle ne peut pas se lier à la même valeur de la clef de la table 'esclave', qui subit donc la jointure.

Pourquoi avoir à sa disposition un 'left outer' et un 'right outer' ?
Dans le cas d'une jointure entre deux tables, c'est totalement équivalent d'inverser les tables comme dans mon dernier exemple.
Mais quand vous avez plus de deux tables dans une jointure, il est parfois casse-tête d'inverser ces tables, d'où alors l'utilité d'avoir un 'left outer' et un 'right outer', qui font exactement la même chose.
Donc, vous devez retenir ceci : l'ordre des tables dans une jointure a de l'importance.

Envoyé par mioummioum

Dans votre dernier message vous me demandiez de créer d'abord mes jointures puis mon calcul mais je ne suis pas arrivée à mieux que ça.

Ce que je vous demandais, c'est de ne pas tout faire en même temps.
La première des choses à faire est de produire une jointure entre toutes vos tables et de vérifier que c'est bien ce que vous attendiez comme résultat.

Ensuite, vous pouvez passer à l'introduction de vos spécificités, sachant que la jointure fonctionne déjà. Si la première étape vous satisfait, elle n'est plus à remettre en cause.
Ensuite, si vous rencontrez un problème lors de la deuxième étape, alors vous savez que la première étape n'est pas en cause.

Quand on est débutant, il faut travailler avec méthode et c'est la méthode que vous appliquez qui vous fait progresser.

Maintenant, je pense que vous avez tout en main, pour créer votre requête. Mais sans jeu d'essai plus consistant, je ne peux rien faire de plus.

@+

**mioummioum** · 25/08/2015, 12h19

Merci à tous les 2, j'apprécie vivement votre aide et vos conseils.

@CinePhil :
dans sqlPro il y a pro

Vous ne pouvez pas me demander en 24h d'être aussi performante que vous. Les erreurs font parties de l'apprentissage je pense et puis je ne suis qu'une fille

concernant la notion de couleurs c'était juste pour intégrer une nouvelle jointure.
"qui" est bien la clef étrangère de la colonne "idd" vous avez vu juste.
L'idée était de faire un calcul de toutes les récidives et d'afficher toutes les infos de tous les détenus même s'ils n'ont pas de couleurs favorites ou de récidives.

@Artemus24 :
super didacticiel, j'ai du mal encore avec la gymnastique left et right, je vais persévérer avec vos exemples très parlants.
je ne vois pas comment faire mon sum. il faut le faire avant ou après ? Comment doit-il s'imbriquer ?

M_M

**CinePhil** · 25/08/2015, 14h08

L'idée était de faire un calcul de toutes les récidives et d'afficher toutes les infos de tous les détenus même s'ils n'ont pas de couleurs favorites ou de récidives.

Comme je l'avais montré dans mon message #6, procédons par étapes...

1) Si j'ai bien compris, vous voulez faire la sommes des durées de détention de chaque détenu ?

Code :

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

1
2
3
4
SELECT qui, 
	SUM(datediff(coalesce(recidives.fin,CURRENT_DATE)), coalesce(recidives.CURRENT_DATE))) AS somme_recidives -- alias pour l'entête de colonne résultat
FROM recidives
GROUP BY qui

2) Récupération des infos du détenu par jointure entre les tables detenus et detention :

Code :

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

1
2
3
4
5
6
7
SELECT d.idd AS id_detenu,
	d.nom,
	SUM(DATEDIFF(COALESCE(r.fin, CURRENT_DATE), COALESCE(r.debut,CURRENT_DATE))) AS somme_recidives
FROM detenus d
LEFT OUTER JOIN recidives r ON r.qui = d.idd
GROUP BY d.idd, d.nom
ORDER BY d.nom

Quelques remarques sur ma requête...
1) "d" et "r" sont des alias de table et je les utilise partout dans la requête pour faciliter son écriture et sa lecture.
2) "idd" n'étant pas un nom de colonne très explicite, je remplace celui-ci par un alias "id_detenu" dans le résultat.
Idem pour la somme que je simplifie dans le résultat en donnant l'alias "somme_recidives.
3) Dans le GROUP BY, je mets toutes les colonnes ne faisant pas l'objet d'un calcul de regroupement.
4) Plutôt que la fonction now(), j'utilise le mot clé standard du SQL : CURRENT_DATE. En plus, ici, il a l'avantage de ne pas ajouter de parenthèses, ce qui évite une éventuelle erreur dans le nombre de parenthèses à fermer.
5) Bonne habitude à prendre : j'écris tous les mots du langage SQL en lettres capitales. Ça permet, notamment quand la requête figure dans un code de programme (exemple, un programme en PHP), de repérer facilement les requêtes. À l'intérieur même de la requête, cela permet de distinguer les noms de tables et colonnes, ainsi que les alias, des mots du langage SQL.
6) Autre bonne habitude à prendre : aérez votre code ! cela en facilitera la lecture et sa compréhension quand vous reviendrez dessus dans plusieurs mois ou années.

Vous essayez de faire la jointure entre detenus et couleurs tout seul pour vérifier que vous avez compris ?

**mioummioum** · 25/08/2015, 15h39

Ok j'ai bien compris vos 6 points à l'exception du point 3. id_detenu c'est un calcul de regroupement du fait de l'alias avec le "AS" ?
car je me posais la question si on pouvait remplacer : GROUP BY d.idd, d.nom par GROUP BY id_detenu, d.nom (c'est peut être une bêtise)

Autrement pour joindre les couleurs j'ai fait ça (libelle c'est le nom de la couleur que j'aurai pu appeler couleur_nom ou nom_couleur

, detenu c'est la colonne de clef étrangère)

Code :

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

1
2
3
4
5
6
7
8
9
SELECT d.idd AS id_detenu,
	d.nom, 
	c.libelle,
	SUM(DATEDIFF(COALESCE(r.fin, CURRENT_DATE), COALESCE(r.debut,CURRENT_DATE))) AS somme_recidives
FROM detenus d
LEFT OUTER JOIN recidives r ON r.qui = d.idd
LEFT OUTER JOIN couleurs c ON c.detenu = d.idd
GROUP BY d.idd, d.nom, c.libelle
ORDER BY d.nom

Idem je pouvais faire ON r.qui = id_detenu au lieu de ON r.qui = d.idd ?
Et ON c.detenu = id_detenu ?

En tout cas MERCI !!!

M_M

**CinePhil** · 25/08/2015, 16h14

j'ai bien compris vos 6 points à l'exception du point 3. id_detenu c'est un calcul de regroupement du fait de l'alias avec le "AS" ?

Non. Le calcul de regroupement, c'est : SUM(DATEDIFF(COALESCE(r.fin, CURRENT_DATE), COALESCE(r.debut,CURRENT_DATE))).
C'est quand même plus pratique d'avoir un résultat avec pour entête de colonne "somme_recidives" plutôt que cette longue formule, non ? Et bien c'est à ça que sert l'alias introduit par le mot clé AS.
À noter que j'aurais pu aussi mettre AS devant les alias de table ou ne pas mettre AS devant l'alias du calcul. Chez MySQL, c'est facultatif.

je me posais la question si on pouvait remplacer : GROUP BY d.idd, d.nom par GROUP BY id_detenu, d.nom

Essayez donc ! Je pense que ça fonctionne, même si je ne fais jamais ainsi. Si vous modifiez votre requête en changeant le nom de l'alias dans le SELECT, il faudra aussi penser à le changer dans le GROUP BY alors que, a priori, le nom de la colonne "idd" ne changera pas.

Autrement pour joindre les couleurs j'ai fait ça

Et vous avez pu constater que ça fonctionne.

Idem je pouvais faire ON r.qui = id_detenu au lieu de ON r.qui = d.idd ?
Et ON c.detenu = id_detenu ?

Essayez donc ! Par contre, là je ne suis pas sûr que ça fonctionne et je déconseille de le faire pour les mêmes raisons que précédemment.

Votre problème est-il

?

**mioummioum** · 26/08/2015, 12h03

Oui c'est vraiment chouette, merci à tous les 2 pour le temps passé à m'aider, m'apprendre, partager...

Je suis comblée, ce problème est pour moi résolu

Une condition dans un SUM

MySQL

Discussions similaires

Partager

Partager