CODEWITHSUNDEEP
phpmysqlsql
David Dubois
David Dubois

Reputation: 129

simplify this SQL request

I have this sql request :

SELECT pl.*, l.loyer, l.charges, l.locataire_id, laire.nom, laire.prenom,
       l.chambre_id, c.numero, c.etage, c.maison_id, m.titre_crm 
FROM
    (
    SELECT spl.id, spl.location_id, spl.mois, spl.annee, spl.loyer_paye
    from locations sl 
        LEFT JOIN
            (
            SELECT * FROM paiement_loyer 
            union 
            SELECT 9999, usl.id, (MONTH(NOW())-1), YEAR(NOW()), 0 
            FROM locations usl 
            WHERE usl.id not in (SELECT location_id FROM paiement_loyer) || 
                                (select count(*) FROM paiement_loyer
                                 WHERE location_id = usl.id AND annee = YEAR(NOW())
              AND mois=(MONTH(NOW())-1) ) = 0
            ) spl ON sl.id = spl.location_id
    where sl.date_debut <= CURDATE() && CURDATE() <= sl.date_fin
    ) pl
JOIN locations l ON pl.location_id = l.id
JOIN locataires laire ON l.locataire_id = laire.id
JOIN chambres c ON l.chambre_id = c.id
JOIN maisons m ON c.maison_id = m.id
ORDER BY trim(upper(m.titre_crm)), c.numero, annee, mois

I would like to simplify it, do you have any idea please ?

Upvotes: 0

Views: 75

Answers (1)

Kickstart
Kickstart

Reputation: 21513

An attempt at cleaning it up. Note that I think the first LEFT OUTER JOIN could probably be swapped to an INNER JOIN.

I have swapped the 2nd UNIONed query to 2 queries, and for those I have changed them to use LEFT OUTER JOINs which then check that there isn't a match

SELECT pl.id, pl.location_id, pl.mois, pl.annee, pl.loyer_paye, 
        l.loyer, l.charges, l.locataire_id, laire.nom, laire.prenom,
       l.chambre_id, c.numero, c.etage, c.maison_id, m.titre_crm 
FROM
(
    SELECT spl.id, spl.location_id, spl.mois, spl.annee, spl.loyer_paye
    FROM locations sl 
    LEFT OUTER JOIN
    (
        SELECT id, location_id, mois, annee, loyer_paye
        FROM paiement_loyer 
        UNION 
        SELECT 9999, usl.id, (MONTH(NOW())-1), YEAR(NOW()), 0
        FROM locations usl 
        LEFT OUTER JOIN paiement_loyer pl1
        ON usl.id = pl1.location_id 
        WHERE pl1.location_id IS NULL
        SELECT 9999, usl.id, (MONTH(NOW())-1), YEAR(NOW()), 0
        FROM locations usl 
        LEFT OUTER JOIN paiement_loyer pl2
        ON usl.id = pl1.location_id 
        AND pl2.annee = YEAR(NOW()) 
        AND pl2.mois=(MONTH(NOW())-1)
        WHERE pl2.location_id IS NULL
    ) spl ON sl.id = spl.location_id
    WHERE CURDATE() BETWEEN sl.date_debut AND sl.date_fin
) pl
JOIN locations l ON pl.location_id = l.id
JOIN locataires laire ON l.locataire_id = laire.id
JOIN chambres c ON l.chambre_id = c.id
JOIN maisons m ON c.maison_id = m.id
ORDER BY TRIM(UPPER(m.titre_crm)), c.numero, pl.annee, pl.mois

Upvotes: 1

Related Questions