Count multiple sur même table et join [Résolu]

[bouket]

Bonjour,

J'ai un petit problème pour regrouper des requêtes sous mysql. Voilà, je possède trois tables comme ceci :

members(name_member,id_member,key_contact,total_emp)
contacts(name_contact,id_contact)
redflag(id_record,id_member,date_added)

mon objectif est de sortir 6 colonnes:
-name_member
-id_member
-name_contact
-total_emp
-new_record
-total_record

Les colonnes name_member, id_member et total_emp sont lisibles directement dans la table members;
La colonne name_contact est déterminé en reliant le key_contact de members avec le id_contact de contacts et en lisant name_contact dans contacts.
Les deux dernières colonnes me posent cependant problème:
-new_record doit sortir le nombre de id_record de chaque id_member sur une période de temps donnée
-total_record la même chose sans restriction de temps.

J'ai réalisé trois requêtes séparées qui semblent fonctionner; je souhaiterais les réunir en une seule, ce qui ne semble pas facile, j'ai essayé avec des RIGHT JOIN ect, mais je piétine...

Pour les 4 premières colonnes:

Code :

1
2
3
SELECT members.name_member, members.id_member, contacts.name_contact, members.total_emp
FROM members, contacts
WHERE contacts.contact_id = members.key_contact

Pour la colonne avec le count sur une période de temps définie

Code :

1
2
3
4
5
6
SELECT members.name_member, members.id_member, members.total_emp, count(redflag.id_record)
FROM members, redflag
WHERE redflag.date_added
BETWEEN '2002-08-22 00:00:00'
AND '2011-08-22 23:59:59'
GROUP BY members.name_member

Pour le count sans période de temps restrictive

Code :

1
2
3
SELECT members.name_member, members.id_member, members.total_emp, count(redflag.id_record)
FROM members, redflag
GROUP BY members.name_member

Je vous remercie par avance pour votre aide,
bouket

[asmduty]

edit : mal lu

[punkoff]

bonjour,

Déjà gros problème avec vos jointures, vous faites des produits cartésiens et non des jointures, lisez ceci : http://sqlpro.developpez.com/cours/sqlaz/jointures/

ensuite problème avec vos group by, entre autre vous utilisez une syntaxe qui peut être interprétée n'importe comment par votre SGBD (c'estl a faute à MySql ca) et vous groupez sur des noms et non des id.
=> que va-t-il se passer quand vous aurez 2 personnes avec le même nom ?
lisez ceci : http://sqlpro.developpez.com/cours/sqlaz/ensembles/


Concernant le problème, vous n'êtes pas loin de la solution, qui pourrai être celle-ci :

Code :

1
2
3
4
5
6
7
8
 
SELECT *
FROM membre a
LEFT OUTER JOIN (
SELECT membre_id, count(*), sum(case when date_added BETWEEN '2002-08-22 00:00:00' AND '2011-08-22 23:59:59' then 1 else 0 end)
FROM redflag
GROUP BY membre_id) b ON a.membre_id = b.membre_id
INNER JOIN contacts c ON c.membre_id = a.membre_id

[CinePhil]

1) Les jointures s'écrivent depuis 1992 avec l'opérateur JOIN.

2) Toutes les colonnes du SELECT ne faisant pas l'objet d'une fonction d'aggrégation doivent figurer dans le GROUP BY.

3) J'ose espérer que, contrairement à l'ordre des colonnes affiché dans ton message, les clés primaires des tables sont bien les id et pas les name ?

4) Commençons par le plus simple.
Inutile de faire une jointure dans la troisième requête.

Code :

1
2
3
SELECT id_member, COUNT(*) AS nb_record
FROM redflag
GROUP BY id_member

5) Idem sur la seconde

Code :

1
2
3
4
SELECT id_member, count(*) AS nb_record_between_dates
FROM redflag
WHERE date_added BETWEEN '2002-08-22 00:00:00' AND '2011-08-22 23:59:59'
GROUP BY id_member

6) On fait des jointures avec les autres tables pour rassembler le tout :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT m.id_member, m.name_member, m.total_emp,
	c.name_contact, 
	t1.nb_record,
	t2.nb_record_between_dates
FROM members m
INNER JOIN contacts c ON c.contact_id = m.key_contact
INNER JOIN 
(
	SELECT r1.id_member, COUNT(r1.*) AS nb_record
	FROM redflag r1
	GROUP BY r1.id_member
) t1 ON t1.id_member = m.id_member
INNER JOIN 
(
	SELECT r2.id_member, count(r2.*) AS nb_record_between_dates
	FROM redflag r2
	WHERE r2.date_added BETWEEN '2002-08-22 00:00:00' AND '2011-08-22 23:59:59'
	GROUP BY r2.id_member
) t2 ON t2.id_member = m.id_member

EDIT :
Grilled by Punkoff qui en plus a pensé au CASE pour ne faire qu'une seule jointure !

[bouket]

merci beaucoup ! ça marche impecc' ! je vais me plonger dans les liens donnés pour réussir à faire ça tout seul !

encore merci