Grosse galère dans un LEFT JOIN [Résolu]

Messages postés
445
Date d'inscription
mardi 1 juillet 2008
Statut
Membre
Dernière intervention
22 juillet 2019
-
Bonjour tout le monde,

Là je n'en peux plus : trois jours que je cherche LA requête, en vain.

Petite base de données. Dans une maison familiale, trois enfants payent des frais qui peuvent être partagés ou individuels. Ci-dessous les tables (NB: Les identifiants Merise, clefs primaires, bien qu'écrits en minuscule dans les tables, sont écrits ici en capitales) :

fct_Personnes(IDPERS,prenom)
+--------+--------+
| idpers | prenom |
+--------+--------+
|      1 | Gwenn  |
|      2 | Jean   |
|      3 | Anne   |
+--------+--------+

fct_Payer(IDFACT,IDPERS,datpay,montant)
+--------+--------+------------+---------+
| idfact | idpers | datpay     | montant |
+--------+--------+------------+---------+
|      1 |      2 | 2019-05-09 |   72.10 |
|      1 |      1 | 2019-05-03 |   72.10 |
|      2 |      1 | 2019-07-21 |   10.55 |
|      3 |      2 | 2019-07-20 |   18.25 |
+--------+--------+------------+---------+

fct_Factures(IDFACT,datfact,numfact,objet,totfact,idtype)
+--------+------------+---------+----------+---------+--------+
| idfact | datfact    | numfact | objet    | totfact | idtype |
+--------+------------+---------+----------+---------+--------+
|      1 | 2019-03-27 |         | EDF      |  171.25 |      2 |
|      2 | 2019-07-15 |         | Peinture |   31.66 |      2 |
|      3 | 2019-07-20 |   F1495 | Vitre    |   18.25 |      1 | 
+--------+------------+---------+----------+---------+--------+

fct_Types(IDTYPE,type)
+--------+--------------+
| idtype | typfact      |
+--------+--------------+
|      1 | Individuelle |
|      2 | Partagée     |
+--------+--------------+


La finalité est d'obtenir pour toutes les factures l'état de tous les paiements qu'ils aient été effectués ou non afin de créer la page en PHP. L'idéal serait d'obtenir :

+--------+------------+---------+----------+---------+--------+--------+--------+------------+---------+
| idfact | datfact    | numfact | objet    | totfact | idtype | idpers | prenom | datpay     | montant |
+--------+------------+---------+----------+---------+--------+--------+--------+------------+---------+
|      1 | 2019-03-27 |         | EDF      |  171.25 |      2 |      1 | Gwenn  | 2019-05-03 |   72.10 |
|      1 | 2019-03-27 |         | EDF      |  171.25 |      2 |      2 | Jean   | 2019-05-09 |   72.10 |
|      1 | 2019-03-27 |         | EDF      |  171.25 |      2 |      3 | Anne   | NULL       |    NULL |
|      2 | 2019-07-15 |         | Peinture |   31.66 |      2 |      1 | Gwenn  | 2019-07-21 |   10.55 |
|      2 | 2019-07-15 |         | Peinture |   31.66 |      2 |      2 | Jean   | NULL       |    NULL |
|      2 | 2019-07-15 |         | Peinture |   31.66 |      2 |      3 | Anne   | NULL       |    NULL |
|      3 | 2019-07-20 |   F1495 | Vitre    |   18.25 |      1 |      2 | Jean   | 2019-07-20 |   18.25 |
+--------+------------+---------+----------+---------+--------+--------+--------+------------+---------+


Où l'on voit que :
- pour la facture 1 (partagée), Gwenn et Jean ont payé leur quote-part mais pas encore Anne.
- pour la facture 2 (partagée), seule Gwenn a réglé sa quote-part mais pas encore Jean et Anne.
- pour la facture 3 (individuelle) a été réglée par l'intéressé;

Voici ci-dessous la requête effectuée. Mais malgré le LEFT JOIN je n'arrive pas à ressortir les paiements non encore effectués :

mysql> SELECT fa.idfact,datfact,numfact,objet,totfact,idtype,pe.idpers,prenom,datpay,montant
    -> FROM fct_Personnes AS pe LEFT JOIN fct_Payer AS pa ON pe.idpers=pa.idpers,fct_Factures AS fa
    -> WHERE pa.idfact=fa.idfact
    -> ORDER BY pa.idfact,pe.idpers;
+--------+------------+---------+----------+---------+--------+--------+--------+------------+---------+
| idfact | datfact    | numfact | objet    | totfact | idtype | idpers | prenom | datpay     | montant |
+--------+------------+---------+----------+---------+--------+--------+--------+------------+---------+
|      1 | 2019-03-27 |         | EDF      |  171.25 |      2 |      1 | Gwenn  | 2019-05-03 |   72.10 |
|      1 | 2019-03-27 |         | EDF      |  171.25 |      2 |      2 | Jean   | 2019-05-09 |   72.10 |
|      2 | 2019-07-15 |         | Peinture |   31.66 |      2 |      1 | Gwenn  | 2019-07-21 |   10.55 |
|      3 | 2019-07-20 |   F1495 | Vitre    |   18.25 |      1 |      2 | Jean   | 2019-07-20 |   18.25 |
+--------+------------+---------+----------+---------+--------+--------+--------+------------+---------+


J'avais pensé faire une requête pour lister toutes les factures et, dans la boucle while() PHP, pour chaque ligne de facture lue faire une requête listant tous les participants impliqués par cette facture (1 seul si la facture est individuelle ou tous les participants si la facture est partagée, qu'ils aient déjà payé ou non).
Mais que suis-je donc allé faire dans cette galère ? Je rame...

Quelqu'un a-t-il une idée sur LA bonne requête ou la démarche à suivre ?

Un super grand merci.


Au cas où des données seraient utiles pour tester, ci-dessous, structure des tables et données :

--
-- Table structure for table `fct_Factures`
--
DROP TABLE IF EXISTS `fct_Factures`;
CREATE TABLE `fct_Factures` (
  `idfact` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID de la facture',
  `datfact` date NOT NULL DEFAULT '0000-00-00' COMMENT 'Date de la facture',
  `numfact` varchar(30) NOT NULL DEFAULT '' COMMENT 'Numéro de la facture',
  `objet` varchar(50) NOT NULL DEFAULT '' COMMENT 'Object de la facture',
  `totfact` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT 'Montant de la facture',
  `idtype` int(11) NOT NULL DEFAULT '0' COMMENT 'Type de la facture (isolée ou partagée)',
  PRIMARY KEY (`idfact`)
) ENGINE=MyISAM AUTO_INCREMENT=4 COMMENT='Table des factures';

INSERT INTO `fct_Factures` VALUES
(1,'2019-03-27','','EDF',171.25,2),
(2,'2019-07-15','','Peinture',31.66,2),
(3,'2019-07-20','F1495','Vitre',18.25,1);

--
-- Table structure for table `fct_Payer`
--
DROP TABLE IF EXISTS `fct_Payer`;
CREATE TABLE `fct_Payer` (
  `idfact` int(11) NOT NULL COMMENT 'ID de la facture',
  `idpers` int(11) NOT NULL COMMENT 'ID du payeur',
  `datpay` date NOT NULL DEFAULT '0000-00-00' COMMENT 'Date de paiement',
  `montant` decimal(8,2) NOT NULL COMMENT 'Montant payé',
  PRIMARY KEY (`idfact`,`idpers`)
) ENGINE=MyISAM COMMENT='Table des paiements';

INSERT INTO `fct_Payer` VALUES
(1,2,'2019-05-09',72.10),
(1,1,'2019-05-03',72.10),
(2,1,'2019-07-21',10.55),
(3,2,'2019-07-20',18.25);

--
-- Table structure for table `fct_Personnes`
--
DROP TABLE IF EXISTS `fct_Personnes`;
CREATE TABLE `fct_Personnes` (
  `idpers` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID du payeur',
  `prenom` varchar(15) NOT NULL DEFAULT '' COMMENT 'Prénom du payeur',
  PRIMARY KEY (`idpers`)
) ENGINE=MyISAM AUTO_INCREMENT=4 COMMENT='Table des personnes';

INSERT INTO `fct_Personnes` VALUES
(1,'Gwenn'),
(2,'Jean'),
(3,'Anne');

--
-- Table structure for table `fct_Types`
--
DROP TABLE IF EXISTS `fct_Types`;
CREATE TABLE `fct_Types` (
  `idtype` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID du type de facture',
  `typfact` varchar(15) NOT NULL DEFAULT '' COMMENT 'Type de la facture',
  PRIMARY KEY (`idtype`)
) ENGINE=MyISAM AUTO_INCREMENT=3 COMMENT='Table des types de factures';

INSERT INTO `fct_Types` VALUES
(1,'Individuelle'),
(2,'Partagée');


Configuration: Dual boot: Windows XP Pro SP3 / Debian Linux

Afficher la suite 

4 réponses

Meilleure réponse
Messages postés
3455
Date d'inscription
jeudi 16 juin 2005
Statut
Membre
Dernière intervention
14 août 2019
913
1
Merci
Bonjour,

En partant du principe que pour chaque facture, on souhaite voir chaque personne en face, je t'invite à regarder la clause CROSS JOIN qui relie chaque ligne d'une table à chaque ligne d'une autre.
Ainsi,
SELECT * FROM fct_Factures f
CROSS JOIN fct_Personnes pe

te donne un bon point de départ.

Ensuite, il suffira de joindre en LEFT OUTER JOIN les paiements, et le tour est joué !
SELECT * FROM fct_Factures f
CROSS JOIN fct_Personnes pe
LEFT OUTER JOIN fct_Payer pa ON pe.idpers = pa.idpers AND pa.idfact = f.idfact
ORDER BY f.idfact

Là tu as presque ce que tu veux, mais il y a des lignes en trop, celles qui concernent les factures individuelles. C'est facilement réglé avec un petit WHERE :
SELECT * FROM fct_Factures f
CROSS JOIN fct_Personnes pe
LEFT OUTER JOIN fct_Payer pa ON pe.idpers = pa.idpers AND pa.idfact = f.idfact
WHERE f.idtype=2 OR (f.idtype=1 AND pa.idfact IS NOT NULL)
ORDER BY f.idfact

Voilà, j'espère que ça te convient :)

Xavier

Dire « Merci » 1

Heureux de vous avoir aidé ! Vous nous appréciez ? Donnez votre avis sur nous ! Evaluez CommentCaMarche

CCM 58271 internautes nous ont dit merci ce mois-ci

heliconius
Messages postés
445
Date d'inscription
mardi 1 juillet 2008
Statut
Membre
Dernière intervention
22 juillet 2019
78 -
Ah là là ! Ce moment où tu restes con parce que quelqu'un t'écrit en deux coups de cuillère à pot ce que tu cherches depuis trois jours !

--- MERCI ---

T'es né comme ça ou c'est dû à 30 années de pratique ? :-)

Je ne vois vraiment pas quoi te dire à part un grand merci et te souhaiter de bonnes vacances si elles ne sont pas encore prises ou une bonne reprise si c'est déjà fait.

Je note la question comme résolue. Merci.
Reivax962
Messages postés
3455
Date d'inscription
jeudi 16 juin 2005
Statut
Membre
Dernière intervention
14 août 2019
913 -
Y a pas encore trente ans de pratique, mais oui, ce genre de problème se retrouve pour une bonne part dans mon métier :)
Bon courage pour la suite
Commenter la réponse de Reivax962
Messages postés
8296
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
19 août 2019
412
0
Merci
bonjour, moi j'essaierais avec un deuxième LEFT JOIN.
ce serait plus clair pour nous si, dans le SELECT, tu mettais le nom de la table avant chaque champ. sinon il nous faut analyser les autres informations.
SELECT fa.idfact,datfact,numfact,objet,totfact,idtype,pe.idpers,prenom,datpay,montant
     FROM fct_Personnes AS pe 
LEFT JOIN fct_Payer AS pa ON pe.idpers=pa.idpers
LEFT JOIN fct_Factures AS fa       ON pa.idfact=fa.idfact
    ORDER BY pa.idfact,pe.idpers
heliconius
Messages postés
445
Date d'inscription
mardi 1 juillet 2008
Statut
Membre
Dernière intervention
22 juillet 2019
78 -
Je viens d'essayer... :
mysql> SELECT fa.idfact,datfact,numfact,objet,totfact,idtype,pe.idpers,prenom,datpay,montant
    -> FROM fct_Personnes AS pe LEFT JOIN fct_Payer AS pa ON pe.idpers=pa.idpers
    -> LEFT JOIN fct_Factures AS fa ON pa.idfact=fa.idfact
    -> ORDER BY pa.idfact,pe.idpers;
+--------+------------+---------+----------+---------+--------+--------+--------+------------+---------+
| idfact | datfact    | numfact | objet    | totfact | idtype | idpers | prenom | datpay     | montant |
+--------+------------+---------+----------+---------+--------+--------+--------+------------+---------+
|   NULL | NULL       | NULL    | NULL     |    NULL |   NULL |      3 | Anne   | NULL       |    NULL |
|      1 | 2019-03-27 |         | EDF      |  171.25 |      2 |      1 | Gwenn  | 2019-05-03 |   72.10 |
|      1 | 2019-03-27 |         | EDF      |  171.25 |      2 |      2 | Jean   | 2019-05-09 |   72.10 |
|      2 | 2019-07-15 |         | Peinture |   31.66 |      2 |      1 | Gwenn  | 2019-07-21 |   10.55 |
|      3 | 2019-07-20 | F1495   | Vitre    |   18.25 |      1 |      2 | Jean   | 2019-07-20 |   18.25 |
+--------+------------+---------+----------+---------+--------+--------+--------+------------+---------+
5 rows in set (0.00 sec)
Commenter la réponse de yg_be
Messages postés
445
Date d'inscription
mardi 1 juillet 2008
Statut
Membre
Dernière intervention
22 juillet 2019
78
0
Merci
Merci pour ta réponse.

OK. Mais je trouve beaucoup moins clair de répéter à chaque fois le nom de la table. Trop d'information tue l'information. Je ne mets, en principe le nom des tables que lorsqu'il y a ambiguïté. Mais si ça peut aider dans la compréhension de la requête, voici, avec tes préconisations et le plus clairement possible celle qui ne remplit pas l'objectif.

SELECT
	fct_Factures.idfact,
	fct_Factures.datfact,
	fct_Factures.numfact,
	fct_Factures.objet,
	fct_Factures.totfact,
	fct_Factures.idtype,
	fct_Personnes.idpers,
	fct_Personnes.prenom,
	fct_Payer.datpay,
	fct_Payer.montant
FROM
	fct_Personnes LEFT JOIN fct_Payer ON fct_Personnes.idpers=fct_Payer.idpers,
	fct_Factures
WHERE
	fct_Payer.idfact=fct_Facture.idfact
ORDER BY
	fct_Payer.idfact, fct_Personnes.idpers;


Tu proposes un second LEFT JOIN. Ok, mais comment l'écrirais-tu ?
Commenter la réponse de heliconius
Messages postés
8296
Date d'inscription
lundi 9 juin 2008
Statut
Contributeur
Dernière intervention
19 août 2019
412
0
Merci
ton modèle me semble bizarre. si c'est une facture individuelle, ne devrait-on pas pouvoir déterminer pour qui?
avec ce modèle, on pourrait faire une requête qui, pour les factures totalement payées, retournerait les paiements faits. pour les factures non totalement payées, la requête retournerait toutes les personnes, et leurs paiements éventuels.
dans ton exemple, ajoute une facture individuelle non payée.
heliconius
Messages postés
445
Date d'inscription
mardi 1 juillet 2008
Statut
Membre
Dernière intervention
22 juillet 2019
78 -
Le modèle n'est pas bizarre. J'essaye de ne pas bidouiller mais de respecter les règles de Merise. L'objet Merise Factures n'a pas à contenir qui va payer. L'éventuel champ "Payeur" dans une table Factures ne peut contenir qu'une valeur et une seule. S'il n'y avait à chaque fois qu'un seul payeur, ce serait bon. Mais si c'est une facture partagée, comment ferais-tu ? La table Factures mentionne si c'est une facture individuelle ou partagée. C'est tout. Si trois ou quatre ou cinq personnes participent au paiement de la facture, il y aura trois, quatre ou cinq occurrences de l'objet Personnes dans la table Payer (et ce, pour une même facture).

Modèle:
[Personnes]-0,n---(payer)---0,n-[Factures]-1,1---(avoir pour)---0,n-[Types]


La relation (payer) est une relation n-aire (toutes les cardinalités maximales sont à n). Il y a donc création d'une table (payer) dont l'identifiant est obtenu par la concaténation des identifiants des objets qui participent à la relation (idfact+idpers) plus éventuellement les propriété portées. Ici :
identifiant de la relation: idpers,idfact
propriétés portées : date de paiement, montant payé
=>
fct_Payer(idfact,idpers, datpay,montant)


La relation (avoir pour [type]) est une relation une-aire (au moins l'une des cmax est à 1). Il n'y a donc pas crétation de table mais migration : l'objet dont la cmax est à 1, reçoit en plus de ses propres champs, l'identifiant de l'autre table (ici: idtype) d'où la table Factures :
=>
 fct_Factures(idfact,datfact,numfact,objet,tofact, idtype)

Le idtype de la table fct_Types a migré dans la table fct_Factures.

Regarde bien la table Payer, elle est tout à fait cohérente. Telle personne a payé pour telle facture telle somme a telle date. S'il est l'unique payeur, le numéro de facture n'apparaîtra qu'une fois et le montant payé correspondra au total de la facture. Si c'est une facture partagée, il y aura plusieurs fois le même numéro de facture pour des personnes différentes et naturellement le montant de tous les paiements sera égal au total de la facture partagée.

Mais je me pose la question de savoir comment tu écris des LEFT JOIN en cascade. Tu peux me montrer, STP ?
Commenter la réponse de yg_be