Requète SQL difficile

[Sarc]

Bonjour

Je bloque un peu sur une requète SQL... Je suis pas un pro du SQL en fait

 SELECT a.id, a.pseudo, count( b.idobj ) , sum( b.qualite ) / count( b.idobj ) AS valeur
FROM membres a, meubles b
WHERE a.id = b.idm
AND count( b.idobj ) >3
GROUP BY b.idm
ORDER BY valeur DESC
LIMIT 0 , 20

Pour un classement des membres, j'aimerais les classer en fonction de "valeur", mais uniquement si count(b.idobj) est strictement supérieur à 3 ! MyAdmin me renvoie l'erreur :

Utilisation invalide de la clause GROUP

Est-ce possible ce que je fais ? Ou je dois tout prendre (donc sans le limit), puis vérifier avec PHP si count(b.idobj) est supérieur à 3, dans ce cas là rajouter dans le classement, sinon chercher le prochain, vérifier qu'on en a 20... Bref, se prendre la tête pour rien

Des pistes s'il vous plaît ?

[-ZN-]

Il faudrait pas ajouter b.idm dans ton select?

[destroyedlolo]

Je n'y connais pas trop en mySQL (je n'utilise que PostgreSQL ou Oracle lorsque je n'ai pas le choix ), mais il me semble bien qu'il faille que ton count()>3 soit dans un subselect.

Sous toute reserve

[Sarc]

Il faudrait pas ajouter b.idm dans ton select?

Pas spécialement, j'en ai pas besoin au final, MySQL peut bien le gérer sans qu'on le prenne dans le tableau final...

Je n'y connais pas trop en mySQL (je n'utilise que PostgreSQL ou Oracle lorsque je n'ai pas le choix wink.gif ), mais il me semble bien qu'il faille que ton count()>3 soit dans un subselect.

Ma foi, tu m'as quand même mis sur la bonne voie ! Ton "peu" de connaissances (ralala modestie.. ) m'a bien aidé, je m'en suis sorti avec une belle requète !

La voici pour ceux qui aiment lire le SQL :

SELECT a.id, a.nomperso, count( b.idobj ) , sum( b.qualite ) / count( b.idobj ) AS valeur
FROM membre a, meuble b
WHERE a.id = b.idm

AND (
SELECT count( idobj )
FROM meuble
WHERE idm = a.id
GROUP BY idm
) >3

GROUP BY b.idm
ORDER BY valeur DESC
LIMIT 0 , 20

J'ai douté jusqu'au dernier moment tu WHERE de la sous requète, s'il allait bien chercher le a.id de la vraie requète... Bref, j'ai eu des doutes, mais ça a l'air de marcher ! J'ai pas fait de test rigoureux, mais il me retourne un tableau logique, donc je pense que c'est bon...

Merci beaucoup destroyedlolo ! D'ailleurs ça m'a donné des idées pour certaines optimisations de tableaux de données ça... Je vais devoir m'y repencher !

[Vincent]

essaie plutot de completer ta requete avec la clause HAVING

SELECT a.id, a.pseudo, count( b.idobj ) as nb , sum( b.qualite ) / count( b.idobj ) AS valeur
FROM membres a, meubles b
WHERE a.id = b.idm
GROUP BY b.idm
HAVING nb >3
ORDER BY valeur DESC LIMIT 0, 20

je ne suis pas sûr du resultat, mais si tu veux que l'on t'aide plus, tu peux aussi poster la structure de ta table avec un echantillion de donnée significatif, comme ca on pourra tester avant de poster des bétises

[Sarc]

Ahah, ça marche aussi

Trop fort, je connaissais pas HAVING en SQL...

Je vais me renseigner dessus tiens !

Merci Vincent, ça fait des requètes moins barbares que celle d'avant qui certes marchait mais devait pomper un peu plus de ressources pour rien...

Pfiou, chaque fois que je pose une question sur le Hub je trouve des maîtres...

[Sarc]

Bon, j'ai encore une requète qui n'est pas géniale...

voilà la table membres :

----------------------

ID | objet

1 | 1

2 | 0

3 | 5

4 | 5

5 | 0

et la table objets :

----------------------

ID | nom

1 | truc

2 | bidule

3 | chouette

4 | tarlatata

5 | youpi

J'aimerais dans une même requète avoir l'id du membre, et le nom de l'objet qu'il a, ou alors une case vide s'il n'a pas d'objet (objet=0)

En gros, j'aimerais un truc du genre (avec l'exemple ci dessus) :

-------------------------------

ID_membre | nom_objet

1 | truc

2 |

3 | youpi

4 | youpi

5 |

Je n'ai pas trouvé comment faire...

Quelqu'un pour m'aiguiller sur celle là ?

[Vincent]

quand je parlais de poster la structure, je voulais dire comme ceci :

-- phpMyAdmin SQL Dump
-- version 2.6.1-pl3
-- [url="http://www.phpmyadmin.net"]http://www.phpmyadmin.net[/url]
-- 
-- Serveur: localhost
-- Généré le : Mardi 19 Juillet 2005 à 21:24
-- Version du serveur: 3.23.58
-- Version de PHP: 4.3.10
-- 
-- Base de données: `gennpdc`
-- 

-- --------------------------------------------------------

-- 
-- Structure de la table `mariages`
-- 

CREATE TABLE mariages (
  MR_IDMR int(10) NOT NULL auto_increment,
  MR_VILLE varchar(25) NOT NULL default 'Dunkerque',
  MR_DATE date NOT NULL default '0000-00-00',
  MR_NOMM varchar(25) NOT NULL default '',
  MR_PRNM varchar(50) NOT NULL default '',
  MR_NOMF varchar(25) NOT NULL default '',
  MR_PRNF varchar(50) NOT NULL default '',
  PRIMARY KEY  (MR_IDMR),
  KEY MR_NOMM (MR_NOMM,MR_NOMF)
) TYPE=MyISAM COMMENT='mariages www.gennpdc.net';

-- 
-- Contenu de la table `mariages`
-- 

INSERT INTO mariages VALUES (1, 'Dunkerque', '1842-03-16', 'ABEELE', 'Louis François Joseph', 'BERNARD', 'Joséphine Françoise');
INSERT INTO mariages VALUES (2, 'Dunkerque', '1851-06-03', 'ABEELE', 'Charles François Joseph', 'DINNEKEIN', 'Mélanie Virginie');
INSERT INTO mariages VALUES (3, 'Dunkerque', '1852-09-04', 'ABEELE', 'Louis François Jospeh', 'MYNGHEER', 'Anne Thérèse');
INSERT INTO mariages VALUES (4, 'Dunkerque', '1866-02-15', 'ABEELE', 'Charles Louis François', 'PRUVOTS', 'Marie Elise Reine');
INSERT INTO mariages VALUES (5, 'Dunkerque', '1866-05-31', 'ABEELE', 'Ferdinand', 'VERHILLE', 'Josephine Françoise Antoinette');
INSERT INTO mariages VALUES (6, 'Dunkerque', '1871-09-29', 'ABEELE', 'Arnoult Joseph', 'AGET', 'Rosalie Josephine Antoinette');
INSERT INTO mariages VALUES (7, 'Dunkerque', '1833-04-17', 'ABELLEY', 'Jean François', 'FREDERYCX', 'Rosalie Donatuie');
INSERT INTO mariages VALUES (8, 'Dunkerque', '1853-05-24', 'ACARIE', 'Charles', 'EGGERICKX', 'Marie Thérèse');
INSERT INTO mariages VALUES (9, 'Dunkerque', '1853-12-28', 'ACARIE', 'Pierre Charles', 'FOURCROY', 'Isabelle Antoinette');
INSERT INTO mariages VALUES (10, 'Dunkerque', '1859-11-09', 'ACARIE', 'Pierre Charles', 'VATIER', 'Louise Victorine Virginie');

comme ca, moi je fais un copier coller chez moi et hop, je peux faire ta requete en 30 sec

mais a vu de nez pour ta question, pourquoi

Select  ID_membre, nom_objet from membres, objets where membres.objet = objets.id

ne convient pas?

[Sarc]

Ouai je sais que tu parlais de ça pour structure, mais c'est le bazar dans ma BDD... Je vais créer des tables avec mon exemple si tu veux

Ce que tu as mis ne marche pas parce que s'il ne trouve pas de nom d'objet, bref si objet = 0, il ne prendra pas du tout la ligne !

Il me retournera :

ID_membre | nom_objet

1 | truc

3 | youpi

4 | youpi

et il me manquera donc des membres...

[Vincent]

et tu aurais pas envie de creer un enregistrement vide ayant pour ID = 0 dans ta table objets?

sinon il y a un t préciser dans ma requete pour que cela fonctionne meme avec du vide

(une histoire de + mais je sais plus ou il faut le mettre, je vais chercher)

[Sarc]

Tiens, cadeau

#
# Structure de la table `membres`
#

CREATE TABLE `membres` (
  `pseudo` varchar(20) NOT NULL default '',
  `objet` int(1) NOT NULL default '0'
) TYPE=MyISAM;

#
# Contenu de la table `membres`
#

INSERT INTO `membres` VALUES ('membre01', 2);
INSERT INTO `membres` VALUES ('membre02', 0);
INSERT INTO `membres` VALUES ('membre03', 3);
INSERT INTO `membres` VALUES ('membre04', 1);
INSERT INTO `membres` VALUES ('membre05', 0);

# --------------------------------------------------------

#
# Structure de la table `objets`
#

CREATE TABLE `objets` (
  `id` int(1) NOT NULL auto_increment,
  `nom_objet` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=4;

#
# Contenu de la table `objets`
#

INSERT INTO `objets` VALUES (1, 'truc');
INSERT INTO `objets` VALUES (2, 'bidule');
INSERT INTO `objets` VALUES (3, 'chouette');
    

J'espère que tu pourras trouver ce qu'il manque... Non je peux pas rajouter une ligne avec id 0, j'ai trop de manipulations avec cette table dans d'autres programmes, je risque des bugs monumentaux avec une ligne "pour rien"... :/

[Portekoi]

Essaie ca pour voir :

Select ID_membre, nom_objet from membres left outer join objets on membres.objet = objets.id

pas tester mais prends 'outer join' pour renvoyer toutes les lignes de la table membre

EDIT :

SELECT pseudo, nom_objet
FROM membres
LEFT OUTER JOIN objets ON membres.objet = objets.id
LIMIT 0 , 30

++

Modifié 19 Juillet 2005 par portekoi

[Sarc]

C'est que ça marche en plus... Bon, prochain achat un livre sur le SQL, il y a vraiment trop de subtilités que je connais pas, et ça me prend la tête... C'est fondamental pourtant ... Par contre, quand je trie par le nom, je me retrouve avec tous les NULL en premier, alors que j'aimerais qu'ils soient à la fin (je sais, je suis capricieux ! ) : c'est possible de mettre les NULL à la fin de l'ordre ?

Merci Portekoi, une fois de plus tu m'aides.. Merci à Vincent aussi pour son aide

[TheRec]

Bonjour,

Une bonne référence pour ce genre de "subtilités" de SQL, qui sont en fait les conceptes de base de SQL : http://www.w3schools.com/sql/default.asp

Les exemples de SQL JOIN sont bien expliqués, malheureusement tout ceci est dans la langue de Shakespear...désolé si cela pose un problème :S

À part ça, tu peux tout à fait utiliser la fonction d'aggrégation AVG() pour calculer la moyenne au lieux de sum()/count() ... Tu as la liste des ces fonctions sur le même site : http://www.w3schools.com/sql/sql_functions.asp

Bonne chance

P.S. : Pour le classement des NULL je te propose plutôt d'essayer de changer de JOIN en fonction du type de relation qu'ont tes tables...demanière à ne récupérer que les enregistrements qui ont une clé correspondant à tes critères...

Modifié 19 Juillet 2005 par TheRec

[Portekoi]

Justement, il veut tout récupérer, d'où le left outer join

Pour le order by j'ai pas bien compris ta demande :

SELECT pseudo, nom_objet
FROM membres
LEFT OUTER JOIN objets ON membres.objet = objets.id
ORDER BY nom_objet desc
LIMIT 0 , 30

?

++

[Vincent]

Par contre, quand je trie par le nom, je me retrouve avec tous les NULL en premier, alors que j'aimerais qu'ils soient à la fin (je sais, je suis capricieux ! ) : c'est possible de mettre les NULL à la fin de l'ordre ?

<{POST_SNAPBACK}>

Pour moi, ce n'est pas possible de manière simple...

le NULL se trie avant un abc ...

comme la clause ORDER BY n'a que ASC est DESC ...

il faut faire compliqué:

tu rajoutes un 3e champ, qui est un flag qui indique si tu as une donnée ou non sur ton 2e champs, ensuite tu tries selon le Flag puis l'ordre alphabetique du 2e champ

[Vincent]

en testant ce que je proposais, voila ce que j'obtiens comme requete :

SELECT pseudo, nom_objet,
IF (
ifnull( nom_objet, 1 ) , "Y", "N"
) AS monFlag
FROM membres LEFT OUTER JOIN objets ON membres.objet = objets.id
ORDER BY monFlag, nom_objet LIMIT 0, 30

reste a voir le volume des données que la requete aura à traiter... parce que je ne sais pas du tout ce que cela donne en temps de charge...

Nous sommes en train de faire compliqué... c'est le serveur qui va payer

[Sarc]

Une bonne référence pour ce genre de "subtilités" de SQL, qui sont en fait les conceptes de base de SQL wink.gif

Mon premier bouquin "PHP et SQL pour les nuls" était très bien, mais restait très simple... Le reste, je l'ai appris un peu sur le tas, et j'avoue que j'ai des lacunes en SQL, qu'il va me falloir combler... Je vais apprendre la langue de Shakespeare en même temps que je vais essayer de comprendre les JOIN, ma foi ça peut pas faire de mal...

So (je commence déjà) merci pour le lien

Merci Vinvent, encore une fois tu as touché du doigt la solution...

Voilà :

SELECT pseudo, nom_objet, objet=0 AS monflag
FROM membres
LEFT OUTER JOIN objets ON membres.objet = objets.id
ORDER BY monflag, nom_objet
LIMIT 0 , 30

J'ai encore appris quelque chose, le objet=0 dans ce qu'on selectionne qui retourne true ou false... J'avoue, encore un truc utile... Ce soir, je me couche moins con !

Merci encore à vous trois pour vos réponses !

EDIT : je n'avais pas vu ton nouveau post, je pense que le mien est moins lourd pour le pauvre serveur