LEFT OUTER JOIN sur plusieurs tables, et résultats en doublons !

[fraid26]

Bonsoir à tous,

voila 2 à 3h que je me prends la tête sur une requete qui devrait être toute simple, le principe :

j'ai une table avec une liste de clients.
Plusieurs table qui représentent des "objets" liés à ces clients (des taches, des échanges, des opportunités...), j'aimerais faire une vue qui reprenne quelques agrégats de ces tables "objets", pour chaque client. Voici le code :

CREATE view bi_vue_1755_personnes_morales as
	SELECT crm_1755_personnes_morales.id_personne_morale,
	
	count(crm_1755_echanges.id_echange) as nombre_echanges,
	count(crm_1755_opportunites.id_opportunite) as nombre_opportunites,
	SUM(crm_1755_opportunites.valeur) as montant_brut_opportunites,
	count(distinct crm_1755_taches.id_tache) as nombre_taches
	
	FROM crm_1755_personnes_morales
	
	LEFT OUTER JOIN
		crm_1755_echanges ON crm_1755_echanges.id_personne_morale = crm_1755_personnes_morales.id_personne_morale
		
	LEFT OUTER JOIN
		crm_1755_opportunites ON crm_1755_opportunites.id_personne_morale = crm_1755_personnes_morales.id_personne_morale
		
	LEFT OUTER JOIN
		crm_1755_taches ON crm_1755_taches.id_personne_morale = crm_1755_personnes_morales.id_personne_morale
		
	WHERE crm_1755_personnes_morales.id_societe='1755'
		and crm_1755_personnes_morales.id_personne_morale='1'
		
	GROUP BY crm_1755_opportunites.id_personne_morale

Le soucis, c'est que les résultats retournés ne sont pas les bons. Par exemple pour tous les champs du type count(), le nombre retourné semble être un produit entre les éléments (par exemple si j'ai 2 tâches et 3 échanges, au lieu de me retourner 2 et 3, il va retourner 6 et 6). j'ai essayé avec des group by table.id_personne_morale pour toutes les tables (en même temps) ça ne change strictement rien.

ça doit être une erreur toute bête, mais là je sèche !

Merci d'avance et bon weekend à tous !

Frédéric

 

[jcaron]

C'est normal, un JOIN effectue un produit cartésien avec tous les lignes des différentes tables jointes qui correspondent. Tu le verras facilement si tu listes juste toutes les lignes obtenues dans GROUP BY/count/sum.

La solution consiste à faire des sous-requêtes, par exemple:

SELECT
    t.id, t.nom, a.nb, b.nb
FROM
    table t
LEFT JOIN
    (SELECT id,count(*) AS nb FROM table2 GROUP BY id) a ON t.id=a.id
LEFT JOIN
    (SELECT id,count(*) AS nb FROM table3 GROUP BY id) b ON t.id=b.id

Table est la table principale (les clients), table2 et table3 sont les tables pour lesquelles tu veux faire les comptes.

Jacques.