tester concordance sur 2 LEFT JOIN [Résolu]

[charlene44]

Bonjour,

Je suis un peu désespérée, je dois réaliser une requête pour me récupérer ce que j'appelle des "campagnes marketing" malheureusement je dois aussi calculer certains statistiques sur ces résultats.

Pour cela je fais appel à 3 tables :

campaign_marketing : contient les données des campagnes
evaluation_action : contient les données des évaluations de façon générale
evaluation_informations : contient les données détaillées de chaque évaluation_action

(je sais les noms sont mal choisis et la base mal architecturée mais malheureusement je dois faire avec)

Mon problème étant que je dois récupérer toutes les campaign_marketing même si ces dernières n'ont pas d'évaluations qui leur sont liées, c'est pourquoi j'utilise des left join.

Or, je dois absolument tester la concordance entre ces 3 tables sur un couple d'identifiant stockés dans campaign_marketing : le id_project et le cpid.

l'id_projet trouve sa concordance dans evaluation_action et le cpid dans evaluation_informations.

Code :

1
2
3
4
5
6
7
8
9
 
SELECT    cm.id_campaign_marketing,
          inf.cpid,
          eva.ID_PROJECT
          [...]
FROM      campaign_marketing cm
LEFT JOIN evaluation_action AS eva ON eva.ID_PROJECT  = cm.id_project 
LEFT JOIN evaluation_informations AS inf ON inf.ID_EVALUATION = eva.id_EVALUATIONACTION AND cm.cpid = inf.CPID
WHERE 1

Pour ce qui est dans champ dans le select, le group by...etc je n'ai aucun soucis, le problème se pose vraiment au niveau des jointures, en effet les informations récupérées dans evaluation_informations sont correct car le test a été fait sur l'id_project dans le premier JOIN et le test sur le cpid est fait sur le deuxième JOIN.

Et voici ma question : dans le premier LEFT JOIN on ne teste que l'id_project comment faire pour tester ce dernier alors qu'il n'est que dans la table evaluation_informations et que je dois passer au préalable par evaluation_action pour établir la concordance entre ces deux tables?

Aidez-moi svp je désespère vraiment

[aieeeuuuuu]

Bonjour,

Je ne suis pas sûr d'avoir bien compris ton problème...

En fait, je crois que ta requête est correcte !

Peux-tu poster les structure des 3 tables...

[charlene44]

ok, je vais vous épargnez tous les champs non concernés par la requête dans ce cas sinon ce sera imbuvable

Le problème comme je l'ai dit est que j'ai un index sur id_project et cpid dans la table campaign_marketing et j'ai besoin de calculer des stats avec les champs d'evaluation_action et d'autres stats avec evaluation_information

Or, cette requête ne teste pas le couple id_projet et cpid dans evaluation_action mais seulement dans evaluation_informations du coup mes stats pour evaluation_action sont faussés.

structure des 3 tables :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
campaign_marketing
id_campaign_marketing
[...]
cpid
id_project

evaluation_action
id_evaluationaction
id_project

evaluation_informations
id_evaluation
cpid

RELATIONS ENTRE LES TABLES
campaign_marketing.id_project = evaluation_action.id_project
campaign_marketing.cpid = evaluation_informations.cpid
evaluation_informations.id_evaluation = evaluation_action.id_evaluationaction

en gros dans ma requete il faudrait que je puisse faire

left join sur evaluation_action AS eva ON (et ici vérifier la concordance id_project comme je le fais déjà ET AUSSI cpid comme je le fais dans le left join de evaluation_informations)

[CinePhil]

Ta question et ta requête laissent supposer qu'il peut y avoir des "inf" qui sont associées à d'autres "eva" que celles de la "cm" auxquelles sont associées les "inf".

Comme tu dis, la BDD est mal foutue !

Peut-être que ceci peut résoudre simplement le problème ?

Code :

1
2
3
4
5
6
7
SELECT cm.id_campaign_marketing,
    inf.cpid,
    eva.ID_PROJECT
FROM campaign_marketing cm
LEFT OUTER JOIN evaluation_action eva ON eva.ID_PROJECT  = cm.id_project
LEFT OUTER JOIN evaluation_informations inf ON inf.ID_EVALUATION = eva.id_EVALUATIONACTION
WHERE cm.cpid = inf.CPID

[aieeeuuuuu]

oui, après une (courte) nuit de sommeil, je pense que je comprend mieux le problème...

En fait si je comprend bien :
Si pour un couple CPID/id_projet donné dans campaign_marketing, il y une ligne dans evaluation_action (même id_projet) et deux lignes dans evaluation_informations (même cpid), alors par exemple un SUM sur une colonne de la table evaluation_action renverra le double de la valeur attendue...

@Cinephil:
Merci, avec ta réponse, j'ai mieux compris la question
par contre, je pense que ton filtre va faire perdre "l'interet" ici du LEFT JOIN...
Car si à l'inverse aucune ligne dans evaluation_informations ne correspond à un cpid donné, le resultat n'incluera pas les lignes de campaign_marketing qui ont ce cpid...

peut être que le plus simple, est alors de faire ainsi, car si j'ai bien compris, le but est de faires des stats, donc j'imagine que charlene44 compte agréger les colonnes des tables eva et inf (surtout qu'elle parle de goup by...)

donc peut être quelque chose comme ceci :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
SELECT 
	cm.id_projet,
	cm.cpid,
	SUM(eva.UneColonne) AS Somme,
	(
		SELECT SUM(UneColonne) 
		FROM  evaluation_informations inf
		WHERE inf.cpid = cm.cpid
			AND inf.id_evaluation = eva.id_evaluationaction
	) AS AutreSomme
FROM campaign_marketing cm
LEFT OUTER JOIN evaluation_action eva 
	ON eva.ID_PROJECT  = cm.id_project
GROUP BY 
	cm.id_projet,
	cm.cpid

Ou alors, (ce qui pourrait etre mieux si plusieurs colonnes de evaluation_informations sont concernées), faire une jointure externe sur une sous requete en pseudo table effectuant déja le group by..., afin de n'avoir au plus qu'une ligne par couple cpid/id_projet

charlene44, pourrais-tu nous donner la requete complète que tu as faite, on y verra peut etre plsu clair...

petite question aussi à charlene44 :
es-tu sûre de ça :

evaluation_informations.id_evaluation = evaluation_action.id_evaluationaction

?

[charlene44]

Désolé pour cette longue absence, vacances oblige ^^

donc pour ma requête, la voici en entier :

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
 
SELECT cm.id_campaign_marketing AS id,
cm.name_campaign AS nameCampaign,
cm.cpid AS cpid,
cm.media asmedia,				 				 	cm.identification_place AS identificationPlace,
cm.mode AS mode,
cm.share AS share,
cm.embed AS embed,
cm.full_screen AS full_screen,
cm.id_project AS idProject, 
campaign.description AS description,
com.compagny_name AS advertiser, 
 
sum(IF((id_evaluation IS NOT NULL), 1, 0)) AS `nbClic`,
sum(IF(`eva`.`ID_USER`!=0, 1, 0)) AS `nbClicIdent`,
sum(IF(`eva`.`EVALUATIONACTION_PROGRESS`=2, 1, 0)) AS `nbAudit`,
sum(IF(`eva`.`EVALUATIONACTION_PROGRESS`=2 AND `eva`.`ID_USER`!=0, 1, 0)) AS `nbAuditIdent`,
sum(IF(`inf`.`CONV_VALID_ACTION`=1, 1, 0)) AS `nbDiagnostic`, avg(IF(`inf`.`QUESTIONNAIRE_TIME`=0, NULL, `inf`.`QUESTIONNAIRE_TIME`)) AS `averageTimeQuestionnaire`,
avg(IF(`inf`.`PROFILE_TIME`=0, NULL, `inf`.`PROFILE_TIME`)) AS `averageTimeResults`
 
FROM (campaign_marketing cm 
LEFT JOIN evaluation_action AS eva ON eva.ID_PROJECT = cm.id_project ) 
LEFT JOIN evaluation_informations AS inf ON inf.ID_EVALUATION = eva.id_EVALUATIONACTION AND cm.cpid = inf.CPID 
 
JOIN campaign ON cm.id_project = campaign.ID_PROJECT 
JOIN compagny AS com ON campaign.id_company_customer = com.id_compagny
JOIN global_rights AS gr ON gr.ID_SLAVE_OBJECT=campaign.ID_PROJECT
AND gr.ID_MASTER_OBJECT='200'
AND   gr.MASTER_OBJECT_TYPE='14'
AND   gr.SLAVE_OBJECT_TYPE ='11'
 
WHERE 1 
GROUP BY cm.cpid, cm.id_project
ORDER BY cm.id_campaign_marketing DESC

Je suis sur que les join notamment sur la table global_rights marchent, ainsi que les calculs de statistiques, le seul problème étant que pour les statistiques tirés de la table evaluation_action (eva donc) les stats ne sont pas bon (nbClicIdent supérieur à nbClic alors que ca ne peut pas être le cas comme vous pouvez le voir) je pense que cela est du au fait que je ne teste la correspondance entre le couple id_project et cpid que dans le deuxieme left join (celui sur inf) et dans le premier left je ne teste que la correspondance sur l'id_project.

si je remplace mes left join par des join simple cela marche, j'ai les bons statistiques mais je ne récupère pas de campaign_marketing si elles n'ont pas d'evaluations rattachées, c'est pourquoi je dois avoir des left join.

Il faudrait réussir à tester le cpid dans le premier left join, rajouter un test comme ceci :

Code :

LEFT JOIN evaluation_action AS eva ON eva.ID_PROJECT = cm.id_project AND cm.cpid = inf.CPID

Or, dans ce premier left join je n'est pas encore établi la correspondance avec inf

[aieeeuuuuu]

Arfff

Code SQL :

1
2
3
4
5
6
7
8
9
10
 
SELECT cm.id_campaign_marketing AS id,
cm.name_campaign AS nameCampaign,
cm.cpid AS cpid,
cm.media asmedia,				 				 	cm.identification_place AS identificationPlace,
...
FROM  
GROUP BY cm.cpid, cm.id_project
...

Les bonnes surprises de MySQL

La liste des colonnes dans la GROUP BY devrait correspondre à la liste des colonnes non agrégées dans ton SELECT, ce qui n'est pas le cas ici...
Je crois que dans ce cas, MySQL "décide" (à l'insu de ton plein grès ) quelle valeur il va choisir pour les colonne non agrégée, mais non groupée.
Je dirai même qu'il ne "décide" pas mais prend sûrement la première qui lui tombe sous la main...
évites ce genre de syntaxe, ça ne peut aboutir qu' à de mauvaises surprises...

Ajoutes toutes tes colonnes (non agrégées) dans ton GROUP BY, et vois si le résultat te semble plus cohérent :

Code SQL :

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
40
41
42
43
44
45
46
47
48
49
 
 
SELECT 
	cm.id_campaign_marketing AS id,
	cm.name_campaign AS nameCampaign,
	cm.cpid AS cpid,
	cm.media asmedia,
	cm.identification_place AS identificationPlace,
	cm.mode AS mode,
	cm.share AS share,
	cm.embed AS embed,
	cm.full_screen AS full_screen,
	cm.id_project AS idProject, 
	campaign.description AS description,
	com.compagny_name AS advertiser, 
 
sum(IF((id_evaluation IS NOT NULL), 1, 0)) AS `nbClic`,
sum(IF(`eva`.`ID_USER`!=0, 1, 0)) AS `nbClicIdent`,
sum(IF(`eva`.`EVALUATIONACTION_PROGRESS`=2, 1, 0)) AS `nbAudit`,
sum(IF(`eva`.`EVALUATIONACTION_PROGRESS`=2 AND `eva`.`ID_USER`!=0, 1, 0)) AS `nbAuditIdent`,
sum(IF(`inf`.`CONV_VALID_ACTION`=1, 1, 0)) AS `nbDiagnostic`, avg(IF(`inf`.`QUESTIONNAIRE_TIME`=0, NULL, `inf`.`QUESTIONNAIRE_TIME`)) AS `averageTimeQuestionnaire`,
avg(IF(`inf`.`PROFILE_TIME`=0, NULL, `inf`.`PROFILE_TIME`)) AS `averageTimeResults`
 
FROM (campaign_marketing cm 
LEFT JOIN evaluation_action AS eva ON eva.ID_PROJECT = cm.id_project ) 
LEFT JOIN evaluation_informations AS inf ON inf.ID_EVALUATION = eva.id_EVALUATIONACTION AND cm.cpid = inf.CPID 
 
JOIN campaign ON cm.id_project = campaign.ID_PROJECT 
JOIN compagny AS com ON campaign.id_company_customer = com.id_compagny
JOIN global_rights AS gr ON gr.ID_SLAVE_OBJECT=campaign.ID_PROJECT
AND gr.ID_MASTER_OBJECT='200'
AND   gr.MASTER_OBJECT_TYPE='14'
AND   gr.SLAVE_OBJECT_TYPE ='11'
 
WHERE 1 
GROUP BY 
	m.id_campaign_marketing,
	cm.name_campaign,
	cm.cpid,
	cm.media ,
	cm.identification_place ,
	cm.mode,
	cm.share,
	cm.embed,
	cm.full_screen,
	cm.id_project, 
	campaign.description,
	com.compagny_name
ORDER BY cm.id_campaign_marketing DESC

Sinon poste un jeu de données car :

si je remplace mes left join par des join simple cela marche,

Laisse supposer que ce n'est pas le problème auquel je pensais...

[charlene44]

merci pour votre aide, j'ai testé vos solutions, avec des sous requetes je me suis approché du résultat mais ce n'était pas ca.

La solution a donc été de créer une vue et de tester le couple cpid et id_project sur cette vue

En tout cas merci beaucoup.