Requête avec IF et COUNT [Résolu]

[arutan]

Bonjour à tous,

Voilà j'ai un problème concernant une requête SQL assez tordu (en tout cas dans ma tête ) mais je suis complètement bloqué...
Voilà j'ai les trois tables suivantes :

• individus (id, nom, prenom, age, etc..) qui correspond à des individus, assez banal quoi..
• ballons(id, couleur, etat, etc..) qui correspond à des ballons disponibles, assez banal aussi...
• possede(id, idIndividu, idBallon, etat) qui correspond à l'association des deux tables, sachant qu'un individu peut avoir plusieurs ballons et que son ballon peut changer d'état (dur, mou, HS)

Ce que je cherche à faire (avec MySQL et sans PHP) c'est de récupérer (en deux champs) :

• le nom de TOUT les individus dans le premier champ
• une valeur 'G' ou 'P' qui dépend des conditions suivantes :

  • G pour ceux qui ont plus de 3 ballons blancs et qui sont durs
  • P pour les autres

Alors avec ceci :

Code :

1
2
3
4
5
6
7
8
SELECT i.nom, p.etat
FROM individu i, ballon b, possede p
WHERE i.id = p.idIndividu
AND p.Individu = b.id
AND b.couleur = 'BLANC'
AND p.etat = 'DUR'
GROUP BY i.nom
HAVING COUNT(*) > 2

J'arrive bien à récupérer le nom des individus qui ont gagné, en fait je récupère bien un tableau sous cette forme :

Code :

1
2
3
4
5
nom  |  etat   
------------
xxx  |  DUR
yyy  |  DUR
zzz  |  DUR

Mais voici mes blocages :

1. Mon premier problème c'est que je ne sais pas comment intégrer cette requête où il n'y a que les gagnants avec une autre requête comprenant aussi les perdants (tout les autres en fait)...

   Pour cela je sens qu'il doit y avoir de l'imbrication mais je ne vois pas du tout comment...

2. Mon deuxième problème c'est que (puisque dans mon tableau final j'aurais aussi les perdants) je ne sais pas du tout comment faire pour que le deuxième chant ne corresponde pas à un champ d'une table mais à mon champ "statut" par exemple, en l'occurrence soit la valeur G ou soit la valeur P... Voilà un exemple :
   
   Code :

   1
   2
   3
   4
   5
   6
   7
   nom  | statut   
   ------------
   aaa  |   P
   xxx  |   G
   bbb  |   P
   yyy  |   G
   zzz  |   G

   Pour cela je me suis dit que je pourrais bien effectuer un IF sur un COUNT... Si le COUNT est > 2, la clause THEN me donnera 'G' et la clause ELSE me donnera le 'P'... Le problème c'est que déjà il y a deux instructions IF dans MySQL

Alors voilà après avoir posé mon problème, à votre avis c'est ma requête qui est tordu ou ma BDD ?
Comment faire à votre avis pour aboutir au résultat ?

Merci d'avance !

[skuatamad]

Qu'est ce que ça donne:

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT nom,
       case when nb_blanc_dur > 2 then 'G' else 'P' end AS statut
  FROM (SELECT i.nom, 
               sum(case when b.couleur = 'BLANC' AND p.etat = 'DUR' 
                        then 1 else 0 
                   end) AS nb_blanc_dur
          FROM individu i
          JOIN possede p ON i.id = p.idIndividu
          JOIN ballon b ON p.Individu = b.id
         GROUP BY i.nom
        ) t

L'idée étant de ne pas filtrer mais de conditionner le décompte.

[arutan]

MAGNIFIQUE !!!
Ça à marché !! Comme quoi ça change tout de savoir ! Ça donne envie d'en apprendre plus !
Merci Skuatamad

Une petite question... À quoi correspond le dernier petit t ?
Je l'ai supprimé pour voir et j'obtiens l'erreur : "#1248 - Every derived table must have its own alias"
Je comprends donc qu'il faut le laisser et que je pourrais par exemple écrire pour le tout premier SELECT : t.nom ; Mais quel en est l'intérêt ?

Dernière question j'ai voulu par exemple filtré les gagnants par équipe (il y a un champ equipe dans la table individu)...
Je comprend facilement qu'il faille rajouter AND i.equipe = 'A'...
Du coup pour tester je l'ai placé :

• dans le premier JOIN (JOIN possede p ON i.id = p.idIndividu AND i.equipe = 'A')
• dans le second (JOIN ballon b ON p.Individu = b.id AND i.equipe = 'A')

Dans les deux cas j'obtiens la même (bonne) réponse.. Y a-t-il une préférence ?

Aussi, à des fin de test (et d'apprentissage) j'ai essayé ces différentes solutions :

• j'ai testé en mettant ON i.equipe = 'A' après le FROM juste avant (FROM individu i ON i.equipe = 'A')... Mais j'ai obtenu une erreur de syntaxe.. Je me suis dis que c'est peut-être parceque à ce moment il n'y a pas encore de jointure (mot-clé JOIN)
• j'ai testé en mettant à la place du ON une clause WHERE (FROM individu i WHERE i.equipe = 'A')
• j'ai aussi testé en mettant une clause HAVING (FROM individu i HAVING i.equipe = 'A')

Bon dans les trois cas j'obtiens une erreur de syntaxe...

En fait comme j'avais pas appris à l'époque à faire les jointure avec les mot-clé JOIN mais plutôt avec des clauses WHERE en série (j'ai lu au passage que cette méthode n'était pas recommendé) ça m'a l'air un peu confus tout ça et je souhaiterai acquérir les bonnes pratiques..

En tout cas merci encore !!

[skuatamad]

À quoi correspond le dernier petit t ?

C'est l'alias de la sous-requête qui peut aussi s'écrire as t.
Il est parfaitement inutile dans ce cas mais comme tu l'as constaté sans alias mysql ronchonne.

j'ai voulu par exemple filtré les gagnants par équipe

C'est un filtre son meilleur emplacement est dans une clause WHERE.
Il existe certains cas où il faut placer (pour répondre au besoin) le filtre dans la clause de jointure (le ON) mais cela concerne les jointures externes :
http://sqlpro.developpez.com/cours/s...ures/#LIII-D-3
Pour les jointures internes c'est inutile.

En fait comme j'avais pas appris à l'époque à faire les jointure avec les mot-clé JOIN mais plutôt avec des clauses WHERE en série (j'ai lu au passage que cette méthode n'était pas recommendé) ça m'a l'air un peu confus tout ça et je souhaiterai acquérir les bonnes pratiques..

Personellement je trouve ça plus lisible notamment pour les jointures externes. Si tu veux en savoir plus, lit le tuto de SQLpro :
http://sqlpro.developpez.com/cours/sqlaz/jointures/

[CinePhil]

Voilà j'ai les trois tables suivantes :

• individus (id, nom, prenom, age, etc..) qui correspond à des individus, assez banal quoi..
• ballons(id, couleur, etat, etc..) qui correspond à des ballons disponibles, assez banal aussi...
• possede(id, idIndividu, idBallon, etat) qui correspond à l'association des deux tables, sachant qu'un individu peut avoir plusieurs ballons et que son ballon peut changer d'état (dur, mou, HS)

Avec ta modélisation, un individu peut posséder plusieurs fois le même ballon et un ballon peut être possédé par plusieurs individus ! Normal ?
En principe, la clé primaire d'une table associative est composée des identifiants des entités participant à l'association si les cardinalités maximales sont à n ou du seul identifiant de l'entité ayant les cardinalités 0,1 dans le cas d'une association (x,n - 0,1).
Donc si tu as cette association :
Individu -0,n----Posséder----0,n- Ballon
=> possede (idIndividu, idBallon, etat)
Un ballon peut toujours être possédé par plusieurs personnes !

Si tu as cette association :
Individu -0,n----Posséder----0,1- Ballon
=> possede (idBallon, idIndividu, etat)

[arutan]

Désolé pour le délai...

Avec ta modélisation, un individu peut posséder plusieurs fois le même ballon et un ballon peut être possédé par plusieurs individus ! Normal ?

Oui c'est bien le cas.. En fait un ballon peut changer de propriétaire mais non il ne peut avoir plusieurs propriétaires.. En fait j'empêche en amont une insertion multiple.. Mais c'est vrai que c'est pas dans l'esprit d'une BDD...

L'idéal serait donc que je revois mes tables...

Sinon comment êtes vous arrivé à cette conclusion ?
Le fait que idIndividu et idBallon ne soient pas des clés (soulignés) suffit-il pour dire qu'ils s'agissent de cardinalité 0-n ?

En fait si j'étais parti en mettant (id,idIndividu, idBallon)
C'est parce que je voulais ajouter une option de date pour avoir un historique des possesseurs...
Mais du coup cela implique des contraintes d'intégrité...
A ce sujet j'avais une question...


Existe-t-il un mécanisme en SQL (avec MySQL en l'occurence) qui permette en supprimant un tuple d'une table (t1) de supprimer dans les autres tables tous les tuples dont la clé est un des attribut du tuple supprimé (de la table t1) ?
En fait je le fait manuellement connaissant la BDD, mais c'est vraiment manuel et sans garanti d'être dépourvu d'oubli...

[CinePhil]

Envoyé par arutan

Oui c'est bien le cas.. En fait un ballon peut changer de propriétaire mais non il ne peut avoir plusieurs propriétaires..

Donc en réalité, et à ce moment du message, ton association est plutôt celle-ci :
Individu -0,n----Posséder----1,1- Ballon

Ce qui donne une clé étrangère dans la table Ballon, référençant l'identifiant de l'individu qui le possède :
Ballon (id, id_proprietaire, couleur, etat...)

Ou bien, si le ballon peut ne pas être possédé :
Individu -0,n----Posséder----0,1- Ballon

Et cette fois on a une table associative, pour empêcher l'apparition du bonhomme NULL dans la clé étrangère, donc la clé primaire est l'identifiant du ballon :
Posseder (id_ballon, id_proprietaire)

Sinon comment êtes vous arrivé à cette conclusion ?
Le fait que idIndividu et idBallon ne soient pas des clés (soulignés) suffit-il pour dire qu'ils s'agissent de cardinalité 0-n ?

La présence de idIndividu et de idBallon ainsi que le nom de la table formé d'un verbe sont de bons indices d'une table associative !

En fait si j'étais parti en mettant (id,idIndividu, idBallon)
C'est parce que je voulais ajouter une option de date pour avoir un historique des possesseurs...

On peut alors avoir le MCD suivant :
Individu -0,n----Posséder----(0,n)- Ballon
Date -(0,n)--------------|

Les cardinalités entre parenthèses indiquent une identification relative, ce qui signifie que l'association "posséder" sera identifiée par le ballon et la date et implique qu'un ballon ne peut être possédé qu'une seule fois à une certaine date.
Ceci donne la table associative suivante :
posseder (id_ballon, date_possession, id_proprietaire)

Mais du coup cela implique des contraintes d'intégrité...
A ce sujet j'avais une question...

Existe-t-il un mécanisme en SQL (avec MySQL en l'occurence) qui permette en supprimant un tuple d'une table (t1) de supprimer dans les autres tables tous les tuples dont la clé est un des attribut du tuple supprimé (de la table t1) ?
En fait je le fait manuellement connaissant la BDD, mais c'est vraiment manuel et sans garanti d'être dépourvu d'oubli...

Avec une contrainte de clé étrangère, tu peux ajouter une contrainte ON DELETE CASCADE.
Et puisque tu utilises MySQL, il faut utiliser le moteur InnoDB pour bénéficier des contraintes de clé étrangère.

[arutan]

Merci pour toutes ces précisions...
Je vais revoir un peu tout cela et selon les besoins je vais voir si je ne vais pas tout remettre à plat et refaire la conception en bonne et due forme..
Dans tout les cas merci encore à CinePhil pour les précisions et surtout à skuatamad pour sa réponse..
Je bascule en résolu (même si par rapport au titre ce n'est pas un IF mais plutôt un CASE...)