Problème sur une requête avec jointure, WHERE, GROUP BY et HAVING

**cvexxx** · 15/01/2011, 22h39

Bonjour à tous !

J'explique mon problème : Je souhaite réaliser la liste de toutes les équipes "non validées" (moins de 2 paiements dans l'équipe).

Voici à présent la requête que j'ai réalisé :

Code :

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

1
2
3
4
5
6
SELECT p.id_equipe, nom_equipe, COUNT(*) AS nb_valide
FROM participer_lan AS p LEFT JOIN equipe AS e ON p.id_equipe = e.id_equipe
WHERE date_reception_paiement>'0000-00-00' AND mode_paiement!='' AND estAnnule=0 AND p.id_lan='8'
GROUP BY p.id_equipe
HAVING COUNT(*) < 2
ORDER BY nb_valide DESC, nom_equipe

Le résultat de la requête est l'affichage de deux équipes dont un joueur validé, il n'y a pas le reste des équipes... Celle où aucun joueur n'est encore validé.

En revanche, la requête qui permet d'afficher la liste des équipes validées fonctionnent parfaitement (2 paiements ou plus dans l'équipe).

Je suppose donc que le problème vient du fait qu'il n'affiche tout simplement pas les équipes avec un "nb_valide" = 0. Or je souhaite justement la liste des équipes avec un nb_valide à 0 ou 1.
Pouvez-vous m'aider à comprendre pourquoi dans la liste des résultats, il n'y a pas d'équipe avec "nb_valide" à 0 ?

J'espère avoir été clair dans mon explication, auquel cas, j'apporterai de plus amples informations.

Merci d'avance !

**bhamp0** · 15/01/2011, 22h50

Y a-t-il un de ces 3 champs qui appartiennent à la table "equipe" ?
date_reception_paiement ? mode_paiement ? estAnnule ?

**cvexxx** · 15/01/2011, 22h52

Non, ces trois champs appartiennent bien à la table "participer_lan".

**f-leb** · 15/01/2011, 23h13

bonsoir,

et en inversant l'ordre des tables:

Code sql :

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

...FROM equipe AS e LEFT JOIN participer_lan AS p...

ça donne quoi ?

**cvexxx** · 15/01/2011, 23h18

Voici la requête modifier en inversant les tables au niveau du FROM :

Code :

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

1
2
3
4
5
SELECT p.id_equipe, nom_equipe, COUNT(*) AS nb_valide
FROM equipe AS e LEFT JOIN participer_lan AS p ON e.id_equipe = p.id_equipe
WHERE date_reception_paiement>'0000-00-00' AND mode_paiement!='' AND estAnnule=0 AND p.id_lan='8'
GROUP BY p.id_equipe
HAVING COUNT(*) < 2 ORDER BY nb_valide DESC, nom_equipe

Le résultat me donne toujours les équipes avec un "nb_valide" à 1 et malheusement pas les équipes avec un "nb_valide à 0.

**f-leb** · 16/01/2011, 13h25

bon,

si on suppose: participer_lan(#id_equipe, #id_tournoi, ...)
parce que participation à des tournois

est-ce que la requête suivante renvoie le bon nombre par équipe:

Code sql :

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

1
2
3
4
5
6
7
8
9
10
SELECT participer_lan.id_equipe, coalesce( count(DISTINCT (SR.id_tournoi)) ,0) AS nbValide
FROM participer_lan
LEFT JOIN 
(
  SELECT p.id_equipe, p.id_tournoi
  FROM participer_lan p
  WHERE p.date_reception_paiement>'0000-00-00' AND p.mode_paiement!='' AND p.estAnnule=0 AND p.id_lan='8'
) SR 
ON participer_lan.id_equipe = SR.id_equipe
GROUP BY participer_lan.id_equipe

?

**cvexxx** · 16/01/2011, 14h25

Alors, j'ai testé ta requête, elle me renvoie 72 équipes sur un total de 79 équipes (équipes contenue dans la table équipe).
La requête ne renvoie pas les 34 équipes non validées (que la requête doit normalement renvoyer).

Je pense que le problème vient de ces filtres :

Code :

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

date_reception_paiement>'0000-00-00' AND mode_paiement!=''

Cela filtrent tout les non validés dont n'affichera jamais les "nb_valide" à 0.

**CinePhil** · 18/01/2011, 14h04

Si je comprends bien ta requête, les deux conditions suivantes vont filtrer les équipes qui ont un paiement :

Code :

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

1
2
p.date_reception_paiement > '0000-00-00' 
    AND p.mode_paiement <> ''

Celles qui n'ont effectué aucun paiement ne seront pas retournées par la requête, sauf si on met la table equipe à gauche de la jointure externe.
Du coup, comme les conditions de restriction portent sur la table de droite, il faut inclure ces conditions dans la condition de jointure.

Essaie comme ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT p.id_equipe, e.nom_equipe, 
    COUNT(*) AS nb_valide
FROM equipe AS e
LEFT JOIN participer_lan AS p  
    ON p.id_equipe = e.id_equipe
    AND p.date_reception_paiement > '0000-00-00' 
    AND p.mode_paiement <> '' 
    AND p.estAnnule = 0 
    AND p.id_lan = '8'
GROUP BY p.id_equipe
HAVING COUNT(*) < 2
ORDER BY nb_valide DESC, nom_equipe

**cvexxx** · 18/01/2011, 18h37

Bonjour,

Merci de ton aide !
Ce n'est pas l'équipe qui est validé par un paiement mais chaque joueurs (appartenant à "participer_lan"). La requête permet de calculer le nombre de paiements dans chaque équipes et de valider ou non une équipe si une équipe à au moins 2 paiements. et la liste des équipes non validés (moins de 2 paiements).

J'ai déjà tenté d'inverser comme tu me l'as précisé dans ta réponse mais le résultat produit toujours la même chose : Cela ne m'affiche toujours pas les "nb_valide" à 0.

Petite question (peut être bête) le moteur MyISAM fonctionne avec les "LEFT JOIN" ? Y a t'il quelque chose que j'aurais oublié ?

**CinePhil** · 18/01/2011, 23h09

Donne la structure des tables concernées et un petit jeu de données ainsi que le résultat attendu par rapport à ces données STP.

**cvexxx** · 19/01/2011, 01h36

Voici la structure de la base de données :

Données de la table "equipe" :

Données de la table "participer_lan" :

Résultat de la requête :

**CinePhil** · 19/01/2011, 09h17

Dans la requête, je vois une colonne "estAnnule" qui n'apparaît pas dans ta structure.

Que veux-tu dire par : "Cela ne m'affiche toujours pas les "nb_valide" à 0." ?
Les équipes dans ce cas n'apparaissent pas dans le résultat ?
Ça t'affiche autre chose dans la colonne résultat "nb_valide" ?

J'ai légèrement corrigé ma requête mais je ne l'ai pas testée :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT e.id_equipe, e.nom_equipe, 
    COUNT(p.id_equipe) AS nb_valide
FROM equipe AS e
LEFT JOIN participer_lan AS p  
    ON p.id_equipe = e.id_equipe
    AND p.date_reception_paiement <> '0000-00-00' 
    AND p.mode_paiement <> '' 
    AND p.estAnnule = 0 
    AND p.id_lan = 8
GROUP BY e.id_equipe, e.nom_equipe
HAVING COUNT(p.id_equipe) < 2
ORDER BY nb_valide DESC, nom_equipe

Dans la précédente, issue de la tienne, on groupait par p.id_equipe. Comme il s'agit d'une colonne de la table de droite, les équipes ne figurant pas dans la table p ne pouvaient pas apparaître dans le résultat.

Petit détail : la condition p.id_lan = 8 sur la table de droite fera que les équipes qui ne participent pas à ce lan seront aussi comptées à zéro. J'ai changé aussi le comptage en ce sens.

Il me semble que la logique de ma requête est bonne. À essayer.

**cvexxx** · 19/01/2011, 18h42

Merci pour ta réponse !

Oui j'ai retiré "estAnnulé" pour simplifier un peu, mais ce champ existe bel et bien dans la table que je dois prendre en compte pour le résultat que j'attends.

"Cela ne m'affiche toujours pas les "nb_valide" à 0." J'aurais plutôt voulu dire : "Cela ne m'affiche pas les "nb_valide à 0.".

Dans les résultats, je n'attends que les équipes (non validées) avec 0 ou 1 paiements de joueurs (suivant que je modifie le HAVING COUNT(p.id_equipe) < x).

Oui alors concernant le "détail", c'est exactement ce que la requête produit. Elle m'affiche bien l'équipe n°36 - Gblob - nb_valide = 1. mais par contre elle m'affiche aussi toutes les équipes à "nb_valide" = 0, toute lan confondu. Et je veux justement filtrer pour ne laisser afficher que les équipes de la lan en question.

**CinePhil** · 19/01/2011, 19h41

Et je veux justement filtrer pour ne laisser afficher que les équipes de la lan en question.

Donc en fait, toutes les infos nécessaires sont dans la table participer_lan et on n'a seulement besoin de joindre avec la table equipe qu'après le regroupement pour récupérer le nom de l'équipe.

Allons-y par étapes...

1) Quelles sont les équipes qui participent à la lan 8 ?
Et en fait profitons-en pour récupérer le nom de l'équipe tout de suite.

Code :

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

1
2
3
4
SELECT DISTINCT e.id_equipe, e.nom_equipe
  FROM participer_lan p
  INNER JOIN equipe e ON e.id_equipe = p.id_equipe
  WHERE id_lan = 8

2) Joignons à gauche cette requête avec la table participer_lan filtrée des équipes ayant des paiements validés et ne retenons que celles qui ont moins de deux paiements :

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
SELECT tmp.id_equipe, tmp.nom_equipe,
  COUNT(p1.id_equipe) AS nb_valide
FROM 
(
  SELECT DISTINCT e.id_equipe, e.nom_equipe
  FROM participer_lan p
  INNER JOIN equipe e ON e.id_equipe = p.id_equipe
  WHERE id_lan = 8
) tmp 
LEFT OUTER JOIN participer_lan p1 
  ON p1.id_equipe = tmp.id_equipe 
  AND p.date_reception_paiement <> '0000-00-00' 
  AND p.mode_paiement <> '' 
  AND p.estAnnule = 0 
GROUP BY tmp.id_equipe, tmp.nom_equipe
HAVING COUNT(*) < 2
ORDER BY COUNT(*) DESC, tmp.nom_equipe

**cvexxx** · 19/01/2011, 21h11

En effet on a besoin de la table équipe que pour récupérer le nom de l'équipe.

Je te remercie de ton aide, je vais étudier le "LEFT OUTER JOIN" que je ne connais pas du tout. La requête fonctionne à merveille

Merci encore !