Select - jointure- count

Version imprimable

05/01/2010, 09h29
tonin10

Select - jointure- count
Bonjour.
Encore une bonne année a tous.
J'ai deux tables
la première contient des dates de cours:
idcours-date-nbrdeplace
1 - 2010-01-10 - 10

la deuxième des participants:
idparticipant - idcours -nom
1- 1 - bibi
2-1- juju

je chercher a faire une requette pour récupérer les dates ou le nb de place est inférieur au nombre de participants
je n'arrive pas a trouver comment faire cette condition

pour l'instant j'ai teste des choses de ce genre
Code:

1 2 3 4 select tabdate.idcours from tabcours left join tabarticipant on tabcours.idcours=tabparticipant.idcours having count(tabparticipant) < tabcours.Nbrdeplace
je sais bien que mon count est mauvais
mais la je bloque
doit falloir des imbriquation de sélect mais la je cale

Si vous pouviez me donner un coup de main ( a défaut d' un coup de pieds u c.... :-) )

si par exemple je veux ajouter des conditions comme selection entre 2 date
where ce mets avant le having et ne gêne en rien?

Merci pour votre aide et conseil
05/01/2010, 10h20
CinePhil
Il te manque la clause de regroupement (GROUP BY). Essaie plutôt comme ça :
Code:

1 2 3 4 5 SELECT p.idcours FROM tabcours AS c LEFT JOIN tabarticipant AS p ON c.idcours = p.idcours GROUP BY p.idcours HAVING count(*) < c.Nbrdeplace
Avec les alias, la requête est plus facile à lire.
05/01/2010, 18h00
tonin10

merci pour ta réponse,
effectivement c'est déja mieux mais par contre la ca ne selectionne que les dates ou y'a deja des inscrits au cours
si par exemple pour un cours y'a pas d'inscrit du coup je l'ai pas.
Je vais deja refaire des tests avec la piste du group by

;)

un truc bizarre
je fais cette requete j'ai une erreur,
Code:

1 2 3 4 5 SELECT c.id_date FROM cours_date AS c JOIN cours_participant as p ON c.id_date=p.id_date GROUP BY p.id_date HAVING count(*) < c.nbr_place
je fais ca, pas d'erreur ?
Code:

1 2 3 4 5 SELECT * FROM cours_date AS c JOIN cours_participant as p ON c.id_date=p.id_date GROUP BY p.id_date HAVING count(*) < c.nbr_place
pour l'erreur il me dis champs inconnu dans la condition having ?

mais j'ai du mal sortir toute les dates sans réservation et avec moins de participants que le nbr max de participant
grrrrrr

05/01/2010, 18h38
plemaire

De base :

Si tu utilise un GROUP BY, c’est que ta SELECT contient des champs de trois types.
- Des constantes, ‘TOTO’
- Ceux qui servent de clef de regroupement, table.colonne
- Ceux sur lesquels on opère l’opération d’agrégation, SUM(table.colonne)

Si tu as :
SELECT A , B , C , Sum(D) , Avg(E), Count(*) , ‘TOTO’
Tu devras forcement avoir :
GROUP BY A , B , C (toto on s’en fou c’est une constante)

Pour le reste, il suffit de considérer que la clause HAVING est l’équivalent pour le GROUP BY de la clause WHERE pour le SELECT.

SELECT
FROM
WHERE
GROUP BY
HAVING

Dans l’ordre.

Le HAVING ne peut filtrer QUE sur les champs de la SELECT.
Dans ton exemple, ni count(*) ni c.nbr_place ne sont dans la SELECT … donc …

Ton raisonnement n’est pas clair :

Citation:

Envoyé par tonin10

je chercher a faire une requette pour récupérer les dates ou le nb de place est inférieur au nombre de participants

Citation:

Envoyé par tonin10

...mais par contre la ca ne selectionne que les dates ou y'a deja des inscrits au cours
si par exemple pour un cours y'a pas d'inscrit du coup je l'ai pas.

Si il n’y a pas de participants, leur nombre ne peut pas être supérieur au nombre de place …

Quelle est ton besoin exactement :p ?

merci pour toutes ces explications,

en fait je veux toutes les dates avec des places libres(id date nbr de place restantes pour cette date).
pour ca j'ai besoin pour chaque date (table cours_date) de compter le nombre de place utilisée ( table cours_participant) et de voir si c'est < au nombre de place (mis dans la table cours_date champs nbr_place)
voici les tables exa peut etre plus clair que mon texte :

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
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE TABLE IF NOT EXISTS `cours_date` (
  `id_date` int(10) unsigned NOT NULL auto_increment,
  `date` date NOT NULL default '0000-00-00',
  `heure` time NOT NULL default '00:00:00',
  `nbr_place` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id_date`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;
 
--
-- Contenu de la table `cours_date`
--
 
INSERT INTO `cours_date` (`id_date`, `date`, `heure`, `nbr_place`) VALUES
(1, '2010-01-05', '18:00:00', 10),
(2, '2010-01-06', '18:00:00', 10),
(3, '2010-01-07', '18:00:00', 10),
(4, '2010-01-08', '18:00:00', 10),
(5, '2010-01-05', '10:00:00', 10);
 
 
CREATE TABLE IF NOT EXISTS `cours_participant` (
  `id_participant` int(10) unsigned NOT NULL auto_increment,
  `id_reservation` int(10) unsigned NOT NULL default '0',
  `nom` varchar(255) NOT NULL default '',
  `prenom` varchar(255) NOT NULL default '',
  `email` varchar(255) NOT NULL default '',
  `tel` varchar(255) NOT NULL default '',
  `id_date` int(10) unsigned NOT NULL default '0',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id_participant`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
 
--
-- Contenu de la table `cours_participant`
--
 
INSERT INTO `cours_participant` (`id_participant`, `id_reservation`, `nom`, `prenom`, `email`, `tel`, `id_date`, `date`) VALUES
(1, 1, 'antonin1', '', '', '', 1, '2010-01-05 10:00:00'),
(2, 1, 'antonin2', '', '', '', 1, '2010-01-05 00:00:00');

cette requete ne fonctionne pas mais faut bien essayer pour comprendre :aie:

Code:

1
2
3
4
SELECT * 
FROM cours_date  as c
WHERE  
(SELECT count(*) from cours_participant as p,cours date as ca where p.id_date=ca.id_date group by p.id_date ) < c.nbr_place

encore merci pour vos aides

06/01/2010, 11h15
plemaire

Bonjour,

J’allais répondre pour ta SELECT et puis je me suis rendu compte qu’il existait d’abord des questions que tu ne t’étais peut être pas posé avant de définir tes tables.

Un participant ne peut pas réserver plusieurs places ?
Là c’est juste une question de choix … à toi de voir.

Par contre la question suivante est plus Embêtante je trouve.

Il ne peut y avoir qu’une seule formation par date ?
C’est aussi un choix mais je trouve ca étrange …

Suggestions :

Ton cours devrait avoir son ID à lui car c’est un objet à part entière (Table de référence).
Ensuite tu peux lui affubler des caractéristiques, comme la date à laquelle il aura lieu, le nombre de places, etc …

Le participant est aussi un objet en soit (table de référence aussi).

La réservation est aussi un objet mais qui lui a une vie plus courte.
C’est une table de convergence avec les deux autres. Mais elle peut aussi apporter des informations propres, comme le résultat des évaluations du cours par les participants à la fin, etc … Enfin ne tombe pas dans l’excès non plus.

Nous voici donc avec Trois Tables, dont deux de référence et une liée aux deux autres.
Mais je ne connais pas le besoin donc ce n’est qu’une suggestion.

Ah autre chose, certains préfèrent les noms de colonnes identiques dans plusieurs tables quand se sont des références, d’autres (comme moi) préfèrent qu’il ne puisse pas y avoir l’ors de l’écriture de la select de « Doute » quand à la table correspondante, donc les noms doivent différés. A toi de voir.

merci plemaire ;)

par rapport a tes suggestions, j'ai fait grossierement ca
chaque cours a un id propre avec date et heure
chaque participant a son id et une table
regarde au dessus j'ai mis la structure de mes tables

en fait la table reservation est la table participant (j'ai une autre table qui est la table client avec un id de reservation id_reservation, qu'on retrouve dans la table participant.

Pour etre plus précis, je ne l'avais pas dis car je pense pas nécessaire pour trouver la requete qui me manque, j'ai une table avec des clients, chaque client peut invité des personnes.

donc niveau relation entre les tables etc... je pense que c'est bon mais par contre la requette que je cherche c'est a dire faire des comptages sur mes tables date de cours et participants, là je coince.

j'ai bien une solution de base
c'est je selectionne toute mes dates en une requette
et apres dans ma boucle je teste la table participants
mais ca m'embete de faire ca je sais qu'on doit pouvoir avoir ce que je veux en une requete unique

je me suis penché hier soir sur les requetes imbriqué , ca doit etre par là ma soluce.

je reformule de facon littérale ce que je souhaites:
Code:

1 2 3 4 5 select " date du cours, nbr de place restante(egale au nombre de place - le nombre de participant) from table date des cours left join table des participant ON table participant id_date = table date. id_date Having (total de participant ) > nbr de place total
pour les colonnes de tables , par habitude sur les id je mets les meme dans les differentes tables.

encore merci de passer du temps à repondre à mon pb
:ccool:

06/01/2010, 13h26
plemaire

Pas de soucis ...

J'ai encore un peu de mal à comprendre .... Quand tu dis :

"Having (total de participant ) > nbr de place total"

Cela signifie que tu cherche à savoir quels sont les formations pour lesquelles tu as trop de participants. C'est bien ca ?
06/01/2010, 13h33
tonin10

oups erreur de ma part
non c'est <
Having (total de participant ) < nbr de place total
excuse
06/01/2010, 13h42
plemaire

Bon ok ;p

Donc la question est :

Sur les cours pas encore pleins, que me reste t'il comme places, y compris si un cours n'a pas encore de participants ?

Voilà pour la question ^^.

Le dernier truc qui me chifonne, c'est l ID du cours.
Est ce que je comprend bien en disant que l'ID, donc la clef d'un cours, c'est sa date et son heure ?

Parceque si tel est le cas, c'est très perturbant.
La dete et l'heure du cours est une "Caractéristique" du cours, pas une clef.
Disont que c'est assez cavalier :p
Il vaudrait mieux un ID non significatif.

De façon générale, tout ce qui sert de "CLEF UNIQUE" ou "ID" ne devrait PAS avoir de signification fonctionnelle. J'entend par là, contenir une information importante.
Par exemple, si j'ai un fichier de personnes (clients ?), sa Clef ne dois pas être "Nom.Prénom" ...
Encore un fois, si on décide de rendre une CLEF "Compréhensible", cela ne dois pas occulter le présence de la donnée en dehors de la clef.
Dans mon exemple précédent, si je décide quand même que ma clef sera nom.prenom, cela ne m'empechera pas d'avoir des colonnes NOM et PRENOM dans ma table ...

De la même facon, si tu veux absolument que la date+heure soit la clef de ton cours, cela ne devrait pas t'empecher d'avoir cette information dans une autre colonne, ou deux JOUR et HEURE.

Toujours une question de choix :p
06/01/2010, 13h49
tonin10

non l'id du cours est id_date
unique en autoincrement
c'est vrai que mon nom n'est pas super bien choisi sa aurait du etre id_cours

Donc au final cela devrait donner quelque chose dans ce genre ...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
        F1.id_date , F1.nbr_place , Count(*)
  FROM
               cours_date          F1
    LEFT JOIN  cours_participant   F2 On (F2.id_date = F1.id_date)
--WHERE .... pas de clause selective pour le moment
-- Mais c'est là qu'il faudrait filtrer les cours souhaités
-- Genre ... F1.date > 'AAAA/MM/JJ' ou autre
GROUP BY
        F1.id_date , F1.nbr_place
  HAVING
        F1.nbr_place > Count(*)

Est ce que je suis loin du résultat ?

06/01/2010, 14h25
tonin10

a mon avis tu dois pas être loin si tu y'es pas déjà (ma femme me dis souvent ca...hummm )
je test voir tout ca
mille merci
06/01/2010, 15h15
tonin10
c'est presque ca, mais y'a un truc qui merdouille
voici le resultat
Code:

1 2 3 4 5 6 7 8 9 id_date nbr_place COUNT( * ) date 1 10 1 2010-01-05 2 10 1 2010-01-06 3 10 1 2010-01-07 4 10 1 2010-01-08 5 10 1 2010-01-05 6 10 1 2010-01-19 7 10 1 2010-01-05
normalement ca devrait etre ca
Code:

1 2 3 4 5 6 7 8 9 id_date nbr_place COUNT( * ) date 1 10 1 2010-01-05 2 10 0 2010-01-06 3 10 0 2010-01-07 4 10 0 2010-01-08 5 10 0 2010-01-05 6 10 0 2010-01-19 7 10 0 2010-01-05
en fait c'est sur la colonne count(*) de resultat qu'il manque qq chose
car si j'ai pas de participant il me mets 1 et si j'ai 1 participant il me mets 1 aussi
par contre si j'ai 2 participants sur une date il me met bien 2 dans la colonne COUNT(*)
06/01/2010, 15h37
plemaire

Oui en effet cette requete ne fait que compter les enregistrements pour une date. C'est à dire au moins 1 puisque l'on est en Left Join.

Essaye peut etre avec Count(Distinct id_participant).

Je crois que ca devrait aller mieux ...
06/01/2010, 20h39
tonin10

je test ca demain
encore merci de ta patience ;)

merchi merchi et encore merchi
je comprend pas tout avec ce count mais ca fonctionne super bien

Code:

1
2
3
4
5
6
 
SELECT F1.id_date, F1.nbr_place, COUNT( DISTINCT id_participant ) 
FROM cours_date F1
LEFT JOIN cours_participant F2 ON ( F2.id_date = F1.id_date ) 
GROUP BY F1.id_date, F1.nbr_place
HAVING F1.nbr_place > COUNT( * )

:ccool:

07/01/2010, 11h33
plemaire

Et bien c'est assez simple,

Le Distinct te permet de savoir pour une clef DATE les code participants différents qui se sont incrits. Là est l'astuce, puisque cette liste peut être vide. Et ensuite le count en fait le comptage.
Donc le zéro peut faire son apparition !!

Le SQL n'est pas fondamentalement très compliqué. Mais parfois il faut "ruser" pour obtenir ce que l'on souhaite.

Pour finir, au sujet des sous requetes, si tu en arrive là c'est que le cas est déjà complexe.
Si tu est obligé, essaye de te limité à des sous requete formant des tables virtuelles, exemple :

Select F1.a , F2.B etc ....
From Nomtable F1
Join (Select X, Y , Z From ....... ) F2 On (X = F1.a , ...)

C'est la manière à mon sens la plus cohérente d'utiliser les sous requetes.
Ou créer une vue aussi, c'est parfois plus rentable.

Si cela ne suffit pas, il faut passer directement, soit aux fonctions, soit au procédures stockées. Le choix entre l'une et l'autre dépendant du caractère "Spécifique(ProcStoc)" ou "Global(Function)" de ce qui est executé.

Bon courage en tout cas... Tu verra, une fois qu'on a compris, cela reste relativement simple.
07/01/2010, 16h26
tonin10

merci pour ces explications,
j'utilise quotidiennement msql mais avec des requettes simples en générale;
c'est avec ce genre de requete et des explications comme les tiennes que je progresse

encore merci
sincérement