Problème perte de données sur jointures multiples

Version imprimable

Bonjour,

Voici un problème qui m'embête depuis longtemps et auquel je ne trouve pas de solutions. Je pense pourtant qu'il y en a une et que c'est la construction de ma requête qui pose problème.

Le contexte :

J'ai une base qui date de plus de 10 ans et qui sert à récolter des chiffres d'affaires de mon entreprises et autres (genre ERP)

Le premier problème est que cette base a mal été conçue à l'origine et qu'elle n'est pas documentée, pas de clés étrangères, pas de schémas (250 tables) et les relations entres les tables ne sont pas claires.

Ensuite la couche php (4) est passée entre plusieurs mains sans documentation dans le code et surtout aucune sécurité n'a été mise en place pour éviter de polluer la base (genre filtre sur les mails, les numéros de tél), voir faire des injections sql -> bref elle est pourrie, et nous nous en dégageons peu à peu pour nous tourner vers un CRM et un vrai ERP, mais cela est un processus long et coûteux, il ne nous reste plus que la partie CA/CRM, le reste ayant été migrer sur des systèmes plus performant et récents (et sûrs).

Nous devons donc continuer à travailler avec ça pendant quelques temps encore.

Ce qui m'intéresse sont en particulier 2 tables qui permettent de suivre le CA ;grosso modo des entreprises versent de l'argent 1 fois par an et je voudrais sortir un bilan sur x annees.

Le schéma des tables est simple pour le coup dans ces tables les données "devraient" être clean, je dis bien devraient.

Table ta_versements : id, id_etse, annee, montant_verse

Table ta_entreprises : id, nom, rattach_idmaitre (pour les filiales)

Je parviens à avoir le CA sur une année sans problèmes :
Code:

1 2 3 4 5 6 7 8 9 10 SELECT te.nom AS nom_etse, ifnull(tem.nom,te.nom) AS nom_maitre, ta.montant_verse AS montant_n FROM ta_versements AS ta LEFT JOIN ta_entreprises AS te ON te.id = ta.id_etse LEFT JOIN ta_entreprises AS tem ON tem.id = te.rattach_idmaitre WHERE ta.annee = 2015
Mais si je souhaites avoir sur 3 ans je n'ai plus les mêmes chiffres, je pense que cela vient de mes jointures et du GROUP BY mais je ne parviens pas à trouver la soultion.

Voici ce qui ce rapproche le plus de la vérité :
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 SELECT ta.id_etse, te.nom AS nom_etse, IFNULL(tem.nom, te.nom) AS nom_maitre, n.montant_verse AS montant_n, n1.montant_verse AS montant_n1, n2.montant_verse AS montant_n2 FROM ta_versements AS ta LEFT JOIN ta_entreprises AS te ON te.id = ta.id_etse LEFT JOIN ta_entreprises AS tem ON tem.id = te.rattach_idmaitre LEFT JOIN ta_versements AS n ON n.id_etse = ta.id_etse AND n.annee = 2015 LEFT JOIN ta_versements AS n1 ON n1.id_etse = ta.id_etse AND n1.annee = 2014 LEFT JOIN ta_versements AS n2 ON n.id_etse = ta.id_etse AND n2.annee = 2013 WHERE ta.annee > 2012 GROUP BY ta.id_etse ORDER BY nom_maitre , nom_etse
Merci de votre aide.

David

09/11/2015, 15h38
ddoumeche

Comment veux tu que nous t'aidions si nous n'avons même pas le schéma de base ?

postes au moins le SHOW CREATE TABLE de chacune de ces tables

Pardon,

voici les 2 tables :

ta_versements:

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
 
CREATE TABLE `ta_versements` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `date_saisie` int(15) NOT NULL,
   `id_etse` int(11) NOT NULL,
   `id_organ_collect` int(11) NOT NULL,
   `montant_organ_collect` decimal(11,2) NOT NULL,
   `id_organ_collect2` int(11) NOT NULL,
   `montant_organ_collect2` decimal(11,2) NOT NULL,
   `annee` int(11) NOT NULL,
   `montant_verse` decimal(11,2) DEFAULT NULL,
   `montant_verse_cat_A` decimal(11,2) NOT NULL,
   `montant_verse_cat_B` decimal(11,2) NOT NULL,
   `montant_verse_cat_C` decimal(11,2) NOT NULL,
   `montant_promesse` decimal(11,2) DEFAULT NULL,
   `etse_remerciee` tinyint(1) NOT NULL,
   `actif` tinyint(1) NOT NULL DEFAULT '1',
   `id_groupe` int(20) NOT NULL DEFAULT '0',
   `YPTYPEVERSEMENT` varchar(40) NOT NULL DEFAULT 'Reversion entreprise',
   PRIMARY KEY (`id`),
   KEY `annee` (`annee`),
   KEY `id_organ_collect` (`id_organ_collect`),
   KEY `etse_remerciee` (`etse_remerciee`),
   KEY `id_groupe` (`id_groupe`),
   KEY `id_etse` (`id_etse`),
   KEY `montant_verse` (`montant_verse`)
 ) ENGINE=MyISAM AUTO_INCREMENT=11562 DEFAULT CHARSET=latin1

et ta_entreprises :

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 `ta_entreprises` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `nom` varchar(255) NOT NULL,
   `nom_courrier` varchar(255) DEFAULT NULL,
   `adresse` varchar(255) DEFAULT NULL,
   `ville` varchar(255) DEFAULT NULL,
   `code_postal` varchar(20) DEFAULT NULL,
   `tel` varchar(20) DEFAULT NULL,
   `fax` varchar(20) DEFAULT NULL,
   `email` varchar(100) DEFAULT NULL,
   `id_nation` int(11) DEFAULT NULL,
   `contact_par` varchar(255) DEFAULT NULL,
   `complement` varchar(255) DEFAULT NULL,
   `perso` varchar(255) DEFAULT NULL,
   `effectif` int(11) DEFAULT NULL,
   `site_web` varchar(255) DEFAULT NULL,
   `code_ape` varchar(10) DEFAULT NULL,
   `contact_stage` varchar(255) DEFAULT NULL,
   `type_relation` int(5) DEFAULT NULL,
   `infos_relations` text,
   `actif` int(1) NOT NULL DEFAULT '1',
   `date_creation` int(15) DEFAULT NULL,
   `date_suppression` int(15) DEFAULT NULL,
   `id_stage` int(11) NOT NULL,
   `secteur_activite` varchar(220) NOT NULL,
   `groupe` tinyint(1) NOT NULL,
   `maitre` tinyint(1) NOT NULL,
   `rattach_idgroupe` int(11) NOT NULL COMMENT 'PLUS UTILISE',
   `rattach_idmaitre` int(11) NOT NULL,
   `prioritaire` tinyint(1) NOT NULL,
   `YBTypeEntite` varchar(20) NOT NULL DEFAULT 'Entreprise',
   `YBPays` varchar(20) NOT NULL DEFAULT 'France',
   PRIMARY KEY (`id`),
   KEY `nom` (`nom`),
   KEY `id_nation` (`id_nation`),
   KEY `actif` (`actif`),
   KEY `type_relation` (`type_relation`)
 ) ENGINE=MyISAM AUTO_INCREMENT=16424 DEFAULT CHARSET=latin1

Bonjour,

Le problème posé là est un simple souci d'agrégation de données.
La 2ème requête composée des LEFT JOIN est tout simplement fausse. La première incomplète.
Code:

1 2 3 4 5 6 7 8 9 10 SELECT te.nom AS nom_etse , ifnull(tem.nom,te.nom) AS nom_maitre, annee , SUM( ta.montant_verse ) AS montant_n FROM ta_versements AS ta LEFT JOIN ta_entreprises AS te ON te.id = ta.id_etse LEFT JOIN ta_entreprises AS tem ON tem.id = te.rattach_idmaitre WHERE ta.annee BETWEEN 2013 AND 2015 GROUP BY te.nom, ifnull(tem.nom,te.nom), annee;
ceci permettra de répondre au besoin. Un agrégat pour l'ensemble des années sélectionnées avec la somme du montant.

Cordialement,

Salut à tous.

Si votre première requête est correcte pour l'année 2015, alors faite un union de la même requête en changeant juste l'année.
C'est le plus simple à faire.

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
SELECT '2015' as 'année',
te.nom AS nom_etse,
ifnull(tem.nom,te.nom) AS nom_maitre,
ta.montant_verse AS montant_n
 
FROM ta_versements AS ta
 
LEFT JOIN ta_entreprises AS te ON te.id = ta.id_etse
LEFT JOIN ta_entreprises AS tem ON tem.id = te.rattach_idmaitre
WHERE ta.annee = 2015
 
union
 
SELECT '2014' as 'année',
te.nom AS nom_etse,
ifnull(tem.nom,te.nom) AS nom_maitre,
ta.montant_verse AS montant_n
 
FROM ta_versements AS ta
 
LEFT JOIN ta_entreprises AS te ON te.id = ta.id_etse
LEFT JOIN ta_entreprises AS tem ON tem.id = te.rattach_idmaitre
WHERE ta.annee = 2014
 
union
 
SELECT '2013' as 'année',
te.nom AS nom_etse,
ifnull(tem.nom,te.nom) AS nom_maitre,
ta.montant_verse AS montant_n
 
FROM ta_versements AS ta
 
LEFT JOIN ta_entreprises AS te ON te.id = ta.id_etse
LEFT JOIN ta_entreprises AS tem ON tem.id = te.rattach_idmaitre
WHERE ta.annee = 2013;

Citation:

Envoyé par Artemus24

Salut à tous.

Si votre première requête est correcte pour l'année 2015, alors faite un union de la même requête en changeant juste l'année.
C'est le plus simple à faire.

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
SELECT '2015' as 'année',
te.nom AS nom_etse,
ifnull(tem.nom,te.nom) AS nom_maitre,
ta.montant_verse AS montant_n
 
FROM ta_versements AS ta
 
LEFT JOIN ta_entreprises AS te ON te.id = ta.id_etse
LEFT JOIN ta_entreprises AS tem ON tem.id = te.rattach_idmaitre
WHERE ta.annee = 2015
 
union
 
SELECT '2014' as 'année',
te.nom AS nom_etse,
ifnull(tem.nom,te.nom) AS nom_maitre,
ta.montant_verse AS montant_n
 
FROM ta_versements AS ta
 
LEFT JOIN ta_entreprises AS te ON te.id = ta.id_etse
LEFT JOIN ta_entreprises AS tem ON tem.id = te.rattach_idmaitre
WHERE ta.annee = 2014
 
union
 
SELECT '2013' as 'année',
te.nom AS nom_etse,
ifnull(tem.nom,te.nom) AS nom_maitre,
ta.montant_verse AS montant_n
 
FROM ta_versements AS ta
 
LEFT JOIN ta_entreprises AS te ON te.id = ta.id_etse
LEFT JOIN ta_entreprises AS tem ON tem.id = te.rattach_idmaitreu
WHERE ta.annee = 2013;

pourquoi faire une UNION de requête, alors que le champ ta.annee contient la donnée?

Un simple IN ( ) suffirait pour reproduire. Mieux encore, un BETWEEN!

12/11/2015, 13h13
Artemus24

Salut Lyche.

Je n'ai pas trop réfléchi au problème de Climberdav. Je suis allé au plus simple.
Entre autre, reprendre la requête qui fonctionne, sans la modifier.
Sinon, oui, je suis d'accord avec toi.

@+
12/11/2015, 14h46
Lyche

Citation:

Envoyé par Artemus24

Salut Lyche.

Je n'ai pas trop réfléchi au problème de Climberdav. Je suis allé au plus simple.
Entre autre, reprendre la requête qui fonctionne, sans la modifier.
Sinon, oui, je suis d'accord avec toi.

@+

pas de soucis ;)

Le fait est, et je n'ai pas pensé à le préciser, que la requête fournis venait de la discussion que j'ai eu avec Climberdav sur le chat dvp. Du coup, la requête été corrigée et testée déjà. Je souhaitais la mettre pour que le sujet ai une réponse et puisse être tagué résolu.

Cordialement,