Agrégat (COUNT) avec 2 jointures => mauvais résultat (MySQL) [Résolu]

[Galdon]

Bonjour

Je cherche à écrire une requête SQL (MySQL) comportant 2 COUNT avec sur chacun des counts une condition.

Pour tester, j'ai créé un exemple simple : une table de conducteurs et une table de voitures :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Structure
CREATE TABLE `ex_conducteur` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `nom` varchar(200) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
 
CREATE TABLE `ex_voiture` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `conducteur_id` int(10) NOT NULL,
  `modele` varchar(250) NOT NULL DEFAULT '0',
  `carburant` varchar(50) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
 
-- Contenu
INSERT INTO `ex_conducteur` (`id`, `nom`) VALUES
(1, 'Bob'),
(2, 'Roger');
 
INSERT INTO `ex_voiture` (`id`, `conducteur_id`, `modele`, `carburant`) VALUES
(1, 1, 'Jeep', 'gazole'),
(2, 1, 'Peugeot 403', 'essence'),
(3, 1, 'Voiture sans permis', 'essence'),
(4, 1, 'Golf', 'essence'),
(5, 2, 'Audi R8', 'essence');

Le but étant d'écrire une requête qui récupère la liste des conducteurs avec 2 colonnes supplémentaire : nombre de voitures essence et nombre de voitures diesel.

J'ai écrit cette requête :

Code :

1
2
3
4
5
6
7
8
SELECT
    ex_conducteur.*,
    COUNT(voiture_essence.id) AS 'nb_voiture_essence',
    COUNT(voiture_gazole.id) AS 'nb_voiture_gazole'
FROM ex_conducteur
LEFT JOIN ex_voiture voiture_essence ON ex_conducteur.id = voiture_essence.conducteur_id AND voiture_essence.carburant = 'essence'
LEFT JOIN ex_voiture voiture_gazole ON ex_conducteur.id = voiture_gazole.conducteur_id AND voiture_gazole.carburant = 'gazole'
GROUP BY ex_conducteur.id

Elle ne retourne pas les bons chiffres, elle indique par exemple que Bob a 3 voitures essence et 3 voitures diesel, c'est faux.

Comment faire ?

Merci

[Waldar]

Vous n'avez pas besoin de faire deux jointures, une seule suffit, et vous pouvez mettre une condition à l'intérieur du count :

Code :

1
2
3
4
5
6
7
8
  SELECT cd.id
       , COUNT(CASE vt.carburant WHEN 'essence' THEN vt.id END) AS 'nb_voiture_essence',
       , COUNT(CASE vt.carburant WHEN 'gazole'  THEN vt.id END) AS 'nb_voiture_gazole'
    FROM ex_conducteur cd
         LEFT OUTER JOIN ex_voiture vt
           ON vt.conducteur_id = cd.id
          AND vt.carburant IN ('essence', 'gazole') -- Nécessaire uniquement si vous avez plus de carburants
GROUP BY cd.id

[boussafi]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
SELECT  * FROM
 
(SELECT
    ex_conducteur.*,COUNT(voiture_gazole.id) AS 'nb_voiture_gazole'
FROM ex_conducteur
LEFT JOIN ex_voiture voiture_gazole ON ex_conducteur.id = voiture_gazole.conducteur_id AND voiture_gazole.carburant = 'gazole'
GROUP BY ex_conducteur.id
)gazole LEFT JOIN
 
 
(SELECT
    ex_conducteur.*,COUNT(voiture_gazole.id) AS 'nb_voiture_essence'
FROM ex_conducteur
LEFT JOIN ex_voiture voiture_essence ON ex_conducteur.id = voiture_gazole.conducteur_id AND voiture_gazole.carburant = 'essence'
GROUP BY ex_conducteur.id
)essence ON gazole.ex_conducteur=essence.ex_conducteur

[Galdon]

@Waldar : classe, je ne connaissais pas cette fonctionnalité SQL (un case sur le COUNT).

C'est simple et ça marche impeccable sur MySQL et même avec mon ORM (doctrine, php).

La seconde solution fonctionne également mais ça devient compliqué à utiliser avec un ORM.

Merci pour votre aide.