Grosse galère dans un LEFT JOIN Résolu/Fermé

Question

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

yg_be · Answer

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 · Answer

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 ?

yg_be · Answer

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.

Reivax962 · Answer

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

Grosse galère dans un LEFT JOIN

4 réponses

Discussions similaires