Discussion :

[gastoncs]

Bonjour,

je vais vous expliquez mon problème assez complexe :

tous d'abord j'ai une table personnel :

avec le matricule de la personne, nom, prenom, statut (id d'une autre table) centre (id d'une autre table) et fonction (id d'une autre table)

ensuite j'ai une table manoeuvres :

avec l'id de la manoeuvre,
le n° de module,
le libelle de la manoeuvre,
le service,
etc.... (moins important)

enfin j'ai ma table pointage qui regroupe les personnes qui on réalise une manoeuvres et la date de réalisation.

pointer_sp est l'id de la personne pointer
pointer_fma est l'id de la manoeuvre pour laquelle il est pointer
et pointer_date correspond à la date du pointage

j'ai crée une requête qui m'affiche le nom des personnes et le nombres de manoeuvres (distincte) qu'ils ont réalisé avec comme condition :
que personnel.pers_centre = 5
et personnel.pers_fonction = 3

la requête fonctionne mais je souhaite ajouter une autre condition qui ne m'afficherais que les manoeuvres de la table pointage pour la quel le service est égal à "EM Formation" ! je ne sais pas ou placer cette condition ???

Voici ma requete :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT CONCAT( p.pers_nom,  ' ', p.pers_prenom,  ' ', g.grd_grade ) AS Nom, COUNT( DISTINCT po.pointer_fma ) AS pourcent
FROM personnels p
JOIN grade g ON g.id_grade = p.pers_grade
LEFT JOIN pointage po ON po.pointer_sp = p.id_pers
CROSS JOIN (
SELECT COUNT( * ) AS nb_manoeuvre
FROM manoeuvres
)m
WHERE p.pers_centre =5
AND p.pers_fonction =3
GROUP BY CONCAT( p.pers_nom,  ' ', p.pers_prenom,  ' ', g.grd_grade ) , m.nb_manoeuvre

donc je me demandé si je ne devrais pas rajouter la condition ici :

Code :

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

 COUNT( DISTINCT po.pointer_fma WHERE manoeuvres.fma_impose = "EM Formation" )

mais ce n'est pas possible biensur !

merci d'avance pour votre aide

[skuatamad]

Utilise CASE

Code :

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

COUNT( DISTINCT case when manoeuvres.fma_impose = 'EM Formation' then po.pointer_fma end)

[gastoncs]

Lorsque je rajoute case j'obtient :

Code :

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

#1054 - Unknown column 'manoeuvres.fma_impose' in 'field list'

[gastoncs]

Je reviens a mon post car j'ai avancé mai il me reste un dernier problème.

le principe général est que je souhaite avec ma requête, afficher le nom, prénom et grade des personnes avec le nombre de manoeuvres distincte réalisé dans la table pointage (cf images ci dessus)

donc voici ma requête qui fonctionne :

Code :

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

1
2
3
4
5
SELECT CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) AS Nom, count( DISTINCT po.pointer_fma ) AS pourcent
FROM personnels p
JOIN grade g ON g.id_grade = p.pers_grade
LEFT JOIN pointage po ON po.pointer_sp = p.id_pers
GROUP BY CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade )

ainsi elle m'affiche :

Pierre Dupont 4
Jacques malin 2
Giséle Dufour 0
Marc pierrot 0

j'ai donc fait une jointure vers table manoeuvres pour n'afficher que les personnes pour la quelle le champ fma_impose = 'EM Formation'
Donc voici la requête :

Code :

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

1
2
3
4
5
6
7
SELECT CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) AS Nom, count( DISTINCT po.pointer_fma ) AS pourcent
FROM personnels p
JOIN grade g ON g.id_grade = p.pers_grade
LEFT JOIN pointage po ON po.pointer_sp = p.id_pers
JOIN manoeuvres ON manoeuvres.id_fma = po.pointer_fma
WHERE manoeuvres.fma_impose = 'EM Formation'
GROUP BY CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade )

Mais la le problème c'est que je n'obtient plus les personne qui n'ont effectuer aucune manoeuvres (c'est a dire les personne dont leur id n'est pas présent dans la table pointage) du coup j'obtient :

Pierre Dupont 4
Jacques malin 2

(du coup marc et giséle sont absent alors qu'il ont 0 manoeuvres de pointé) comment résoudre ce soucis ????
c'est comme si le LEFT JOIN avait disparu !!!

[estofilo]

La condition de JOIN suivante:
ON manoeuvres.id_fma = po.pointer_fma
impose que po.pointer_fma ne soit jamais NULL, et ça contredit le LEFT JOIN qui précède, c.a.d que ça le rend équivalent à un INNER JOIN

Pour que po.pointer_fma puisse être null, il est impératif de ne pas l'utiliser dans des jointures internes. Généralement pour arriver à ça, on remplace simplement ces jointures internes par des jointures externes (LEFT JOIN ou RIGHT JOIN)

[gastoncs]

oui j'avais essayer avec LEFT JOIN a la place du JOIN ou RIGHT JOIN mais toujours le même résultat je n'ai pas les personne égal à zéro (ce qui ne sont pas dans la table pointage)

Code :

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

1
2
3
4
5
6
7
SELECT CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) AS Nom, count( DISTINCT po.pointer_fma ) AS pourcent
FROM personnels p
JOIN grade g ON g.id_grade = p.pers_grade
LEFT JOIN pointage po ON po.pointer_sp = p.id_pers
LEFT JOIN manoeuvres ON manoeuvres.id_fma = po.pointer_fma
WHERE manoeuvres.fma_impose = 'EM Formation'
GROUP BY CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade )

[estofilo]

le LEFT JOIN manoeuvres indique qu'il n'y a pas forcément de correspondance dans manoeuvres.
Mais c'est (encore) contredit par la clause :

Code :

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

WHERE manoeuvres.fma_impose = 'EM Formation'

[gastoncs]

??? je ne comprend pas

[estofilo]

Pour les personnes n'ayant pas fait de manoeuvre, manoeuvres.fma_impose va être à NULL et donc la condition manoeuvres.fma_impose = 'EM Formation' intervenant après les jointures va éliminer ces personnes.

Une solution pourrait être de mettre ce filtrage avant le JOIN, en remplaçant manoeuvres par une sous-requête:

Code :

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

1
2
3
4
5
6
7
SELECT CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) AS Nom, count( DISTINCT po.pointer_fma ) AS pourcent
FROM personnels p
JOIN grade g ON g.id_grade = p.pers_grade
LEFT JOIN pointage po ON po.pointer_sp = p.id_pers
LEFT JOIN (select id_fma FROM manoeuvres WHERE fma_impose = 'EM Formation') AS ma
 ON ma.id_fma = po.pointer_fma
GROUP BY CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade )

[gastoncs]

merci
beaucoup

[gastoncs]

en faite je viens de me rendre compte que les conditions sur manoeuvres ne fonctionne pas avec cette requête, alors j'ai fait ceci :

Code :

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

1
2
3
4
5
6
7
SELECT CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) AS Nom, count( DISTINCT po.pointer_fma ) AS pourcent
FROM personnels p
JOIN grade g ON g.id_grade = p.pers_grade
LEFT JOIN pointage po ON po.pointer_sp = p.id_pers
LEFT JOIN manoeuvres ma ON ma.id_fma = po.pointer_fma
WHERE ma.fma_impose = 'EM Formation'
GROUP BY CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade )

mais le probleme maintenant c'est que je n'ai pas les personnes qui n'ont effectué aucune maneouvre, ceux égal à 0 mais j'ai bien la condition EM Formation !!!

[estofilo]

Je ne vois pas de différence entre cette requête et la version du message #6 qui ne donnait pas satisfaction.

Quand tu écris "ça ne fonctionne pas", c'est insuffisant pour se faire une idée du problème.

Indique les données qu'il y a dans les tables
Indique la sortie de la requête à laquelle tu t'attends
Indique la sortie de la requête que tu obtiens par opposition au résultat attendu.

Pour que quelqu'un puisse reproduire le problème et tester la requête, idéalement il faut la définition des tables sous forme de CREATE TABLE et le remplissage de données d'exemple sous forme d'INSERT

A partir d'un certain de niveau de complexité de requête, on ne peut pas aider sans tester ce qu'on propose.

[gastoncs]

oui tu a raison je comprend, c'est moi qui vais trop vite, donc je m'explique :

donc tout d'abord voici ma table personnel :

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
--
-- Structure de la table `personnels`
--
 
CREATE TABLE IF NOT EXISTS `personnels` (
  `id_pers` int(11) NOT NULL AUTO_INCREMENT,
  `pers_matricule` varchar(20) NOT NULL,
  `pers_grade` int(11) NOT NULL,
  `pers_nom` varchar(70) NOT NULL,
  `pers_prenom` varchar(70) NOT NULL,
  `pers_statut` int(11) NOT NULL,
  `pers_centre` int(11) NOT NULL,
  `pers_fonction` int(11) NOT NULL,
  PRIMARY KEY (`id_pers`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=239 ;

Ensuite j'ai ma table grade :

Code :

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

1
2
3
4
5
6
7
8
9
--
-- Structure de la table `grade`
--
 
CREATE TABLE IF NOT EXISTS `grade` (
  `id_grade` int(11) NOT NULL AUTO_INCREMENT,
  `grd_grade` varchar(20) NOT NULL,
  PRIMARY KEY (`id_grade`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

Ensuite ma table pointage :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Structure de la table `pointage`
--
 
CREATE TABLE IF NOT EXISTS `pointage` (
  `id_pointer` int(11) NOT NULL AUTO_INCREMENT,
  `pointer_sp` int(11) NOT NULL,
  `pointer_fma` int(11) NOT NULL,
  `pointer_date` date NOT NULL,
  `pointer_formateur` int(11) NOT NULL,
  `pointer_valideur` int(11) NOT NULL,
  `pointer_formateur2` int(11) NOT NULL,
  `pointer_formateur3` int(11) NOT NULL,
  PRIMARY KEY (`id_pointer`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=174 ;

et enfin ma table manoeuvre :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Structure de la table `manoeuvres`
--
 
CREATE TABLE IF NOT EXISTS `manoeuvres` (
  `id_fma` int(10) NOT NULL AUTO_INCREMENT,
  `fma_module` varchar(50) NOT NULL,
  `fma_libelle` varchar(250) NOT NULL,
  `fma_impose` varchar(100) NOT NULL,
  `fma_fonction` int(11) NOT NULL,
  `fma_fonction2` int(11) NOT NULL,
  `fma_fonction3` int(11) NOT NULL,
  `fma_fonction4` int(11) NOT NULL,
  PRIMARY KEY (`id_fma`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=53 ;

et donc moi je souhaite afficher le nom, prenom et grade des personnes (avec comme condition que le champ pers_centre = 1 et que pers_fontion = 2) qu'ils soient présentent ou non dans la table pointage ainsi que le nombre de manoeuvres qu'il ont réalisé (avec comme condition, sur le champ fma_impose = 'EM Formation' et aussi que fma_fonction = 2) :

donc voici la requête (proposé par 'estofilo') :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) AS Nom, count( DISTINCT po.pointer_fma ) AS pourcent
FROM personnels p
JOIN grade g ON g.id_grade = p.pers_grade
LEFT JOIN pointage po ON po.pointer_sp = p.id_pers
LEFT JOIN (SELECT id_fma FROM manoeuvres WHERE fma_impose = 'EM Formation' AND fma_fonction2 = 2) AS ma
 ON ma.id_fma = po.pointer_fma
WHERE p.pers_centre =  1
AND p.pers_fonction = 2
GROUP BY CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade )

le soucis est qu'avec cette requête les conditions

Code :

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

WHERE fma_impose = 'EM Formation' AND fma_fonction2 = 2

ne sont pas prises en compte !

alors j'ai essayé à ma sauce de faire cette requête qui fonctionne c'est a dire qu'elle prend bien en compte les 2 conditions ci dessus mais elle ne m'affiche pas les personnes qui sont absente de la table pointage (cad avec pourcent = 0)

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade ) AS Nom, count( DISTINCT po.pointer_fma ) AS pourcent
FROM personnels p
JOIN grade g ON g.id_grade = p.pers_grade
LEFT JOIN pointage po ON po.pointer_sp = p.id_pers
LEFT JOIN manoeuvres ma ON ma.id_fma = po.pointer_fma
WHERE p.pers_centre = 1
AND p.pers_fonction = 2
AND ma.fma_impose = 'EM Formation'
AND ma.fma_fonction = 2
GROUP BY CONCAT( p.pers_nom, ' ', p.pers_prenom, ' ', g.grd_grade )

c'est pour cette raison que je m'en remet à vous, d'avance merci pour l'attention que vous portez a mon probléme !

[punkoff]

Bonjour,

Vous n'avez pas compris les explications d'Estofilo dans ce cas.

Lisez ceci : http://blog.developpez.com/cinephil/...on-de-restric/

[CinePhil]

Comme ça, ça devrait fonctionner :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT p.pers_nom, p.pers_prenom, g.grd_grade, 
	count( DISTINCT po.pointer_fma ) AS nombre_manoeuvres
FROM personnels p
INNER JOIN grade g ON g.id_grade = p.pers_grade
LEFT JOIN pointage po ON po.pointer_sp = p.id_pers
	LEFT JOIN manoeuvres ma 
		ON ma.id_fma = po.pointer_fma
		AND ma.fma_impose = 'EM Formation'
		AND ma.fma_fonction2 = 2
WHERE p.pers_centre = 1
	AND p.pers_fonction = 2
GROUP BY p.pers_nom, p.pers_prenom, g.grd_grade

Nota :
J'ai supprimé le CONCAT, ce qui permettra au SGBD d'utiliser les index, s'ils existent, sur les colonnes de groupage et rendre ainsi la requête plus performante.

Une requête correctement indentée est plus facile à lire donc à comprendre et à débugguer.

[ericd69]

salut,

petits conseils vu que tu en es aux tous débuts de ton application de gestion de stage...

préciser la valeur entre () pour les entier ne fait que préciser le nombre de chiffres significatif pas la taille de la colonne en octets

la seule façon de le faire est d'utiliser un des types d'entier existant

si je comprends bien tu fais ça pour les pompiers...

donc dimensionnes les identifiants en conséquence:
effectif des pompiers: 250000 donc ça tient sur du:

Code sql :

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

mediumint unsigned

nombre de centres: 7277 donc:

Code sql :

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

smallint unsigned

les grades moins de 255 donc:

Code sql :

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

tinyint unsigned

idem pour status et fonction du personnel

si une valeur n'a pas à être négative autan la déclarer en unsigned sous peine de perdre potentiellement l'équivalent de la moitié des valeurs possibles (la partie négative)

nommes tes contraintes et index, ça permet d'avoir un nom parlant lors d'un explain sur une requête

mets toujours les types numériques classés par taille en premier (index) puis les dates, booléen et enfin les dérivés de char, binary ou text, ça permet d'optimiser l'utilisation des buffers de table pour chaque ligne

préfère la suppression et recréation à la création en cas de non existence, ça te garantit que si tu change ta structure ça le mettra bien à jour...

ça donnerait pour tes tables:

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
drop table if exists `personnels`;
CREATE TABLE `personnels` (
  `id_pers` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `pers_centre` smallint unsigned NOT NULL,
  `pers_grade` tinyint unsigned NOT NULL,
  `pers_statut` tinyint unsigned NOT NULL,
  `pers_fonction` tinyint unsigned NOT NULL,
  `pers_nom` varchar(70) NOT NULL,
  `pers_prenom` varchar(70) NOT NULL,
  `pers_matricule` varchar(20) NOT NULL,
  constraint pk_pers PRIMARY KEY (`id_pers`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=239 ;

et donc:

Code sql :

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

1
2
3
4
5
6
drop table if exists `grade`;
CREATE TABLE `grade` (
  `id_grade` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `grd_grade` varchar(20) NOT NULL,
  constraint pk_grade PRIMARY KEY (`id_grade`)
) ENGINE=myisam  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

j'espère que tu sais à quoi ça sert de préciser AUTO_INCREMENT=valeur dans la déclaration de table?

les tables avec peu de changement comme personnel ou surtout grade, statut devraient être en myisam car tu te fous de garantir une mise à jour vu que ces dernières ne bougeront pas... pour personnel ça dépend ce que tu veut garantir comme mises à jours...

pour le jeu de caractère utf-8 est pas plus mal que latin1 et il y a peu de différences (1 octet par caractère au max) après question de gout...

autan en myisam les clés étrangères ne sont pas prise en compte autan en innodb... oui... donc dans pointage et manoeuvre on devrait les voir... sachant que ça crée automatiquement l'index sur ces colonnes

par exemple:

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
12
drop table if exists `pointage`;
CREATE TABLE `pointage` (
  `id_pointer` int unsigned NOT NULL AUTO_INCREMENT,
  `pointer_fma` int unsigned NOT NULL,
  `pointer_sp` mediumint unsigned NOT NULL,
  `pointer_formateur` mediumint unsigned NOT NULL,
  `pointer_formateur2` mediumint unsigned NOT NULL,
  `pointer_formateur3` mediumint unsigned NOT NULL,
  `pointer_valideur` mediumint unsigned NOT NULL,
  `pointer_date` date NOT NULL,
  constraint pk_pointer PRIMARY KEY (`id_pointer`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=174 ;

ici les questions à se poser:
est ce que tu fais 1 entrée de pointage (en répétant tout) par sapeur qui suit la formation? (horreur)
est ce que tu as systématiquement 3 formateurs?
ce qui engendrerait un remaniement de ce truc qui ne fera que gonfler avec le nombre de manips...
une fois que c'est fait mettre les définition de clés étrangères sur ce qui en aura encore besoin...

autre problème sur `manoeuvres` au niveau de `fma_impose`... tu nous crée une pauvre colonne de 100 caractères, définissant des valeurs clairement répétitives et tu fais une jointure (ou des recherches) dessus sans l'indexer
la seule punition pour ça est:
ces valeurs devraient être mises dans une table `fma_impose` (myisam) et dans `manoeuvres` tu ne devrais avoir qu'un pointeur sur elle... et là tu rendrais tes tables innodb et jointures drastiquement plus compactes et rapides quand tu va avoir ton nombre de manips qui va être conséquent...

à toi de voir si ça vaut pas le coup d'y penser et de revoir ton modèle de données à ce stade...

[gastoncs]

merci ericd69, pour toutes cette mines d'informations, je vais prendre en compte des remarques et modifier mes tables comme il se doit, car heureusement je n'en suis que au début. en tout cas merci ca m'est bien utile