Écrire la requête complète

Un rapport qui joint directement les emprunts à leurs retards peut répéter des lignes et gonfler les totaux. Vous allez construire une requête complète qui évite ce piège et conserve aussi les adhérents sans emprunt.

Le résultat comporte une ligne par adhérent, le total de ses emprunts, le nombre de ses retards encore en cours et les titres des œuvres concernées. Pour rester cohérent avec la spécification du rapport, le périmètre des emprunts comprend ici tous les emprunts enregistrés ; un retard est un emprunt dont la date d’échéance est passée et qui n’a pas été rendu.

Préparer les agrégations séparément

La requête calcule d’abord un total par adhérent dans une sous-requête utilisée comme table dérivée. Une seconde sous-requête calcule, sur le même principe, les retards et les titres associés. Ces résultats sont ensuite reliés à la table des adhérents : une absence de correspondance donne un total nul, sans écarter la personne du rapport.

Écrire la requête complète

-- Une ligne par adhérent : emprunts, retards en cours et œuvres concernées.
SELECT
    a.id_adherent,
    a.nom,
    a.prenom,
    COALESCE(e.nombre_emprunts, 0) AS nombre_emprunts,
    COALESCE(r.nombre_retards, 0) AS nombre_retards,
    COALESCE(r.oeuvres_concernees, 'Aucune') AS oeuvres_concernees
FROM adherents AS a
LEFT JOIN (
    -- Compter les emprunts enregistrés pour chaque adhérent.
    SELECT
        em.id_adherent,
        COUNT(*) AS nombre_emprunts
    FROM emprunts AS em
    GROUP BY em.id_adherent
) AS e
    ON e.id_adherent = a.id_adherent
LEFT JOIN (
    -- Ne retenir que les emprunts encore en retard.
    SELECT
        em.id_adherent,
        COUNT(*) AS nombre_retards,
        GROUP_CONCAT(
            DISTINCT o.titre
            ORDER BY o.titre
            SEPARATOR ', '
        ) AS oeuvres_concernees
    FROM emprunts AS em
    INNER JOIN exemplaires AS ex
        ON ex.id_exemplaire = em.id_exemplaire
    INNER JOIN oeuvres AS o
        ON o.id_oeuvre = ex.id_oeuvre
    WHERE em.date_echeance < CURRENT_DATE
      AND em.date_retour IS NULL
    GROUP BY em.id_adherent
) AS r
    ON r.id_adherent = a.id_adherent
ORDER BY a.nom, a.prenom, a.id_adherent;

Dans la première sous-requête, COUNT(*) mesure les emprunts enregistrés de chaque adhérent. Dans la seconde, le filtre de retard est appliqué aux lignes avant le regroupement ; les jointures vers les exemplaires et les œuvres fournissent les titres à afficher. GROUP_CONCAT rassemble ces titres dans une seule valeur, séparés par une virgule, et DISTINCT évite de répéter un titre si plusieurs exemplaires de la même œuvre sont en retard.

Les deux jointures externes préservent tous les adhérents, y compris ceux qui n’ont ni emprunt ni retard. Les agrégats ne produisent aucune ligne pour ces personnes ; COALESCE remplace alors les valeurs absentes par zéro ou par le texte « Aucune ». Le tri final donne un ordre stable par nom, prénom et identifiant.

Comprendre le résultat

Le résultat expose une seule ligne par adhérent grâce à la granularité des deux regroupements et à leur rattachement par identifiant. Par exemple, un adhérent avec trois emprunts, dont un en retard portant sur « Le Rivage », obtient les valeurs 3, 1 et Le Rivage. Un adhérent sans emprunt apparaît avec 0, 0 et Aucune.

Cette requête est un rapport SQL autonome : elle ne modifie pas la page index.html ni la feuille style.css. Elle suppose que les colonnes utilisées existent dans le schéma de la médiathèque, avec les identifiants reliant les emprunts, les adhérents, les exemplaires et les œuvres.

À retenir

• Définissez d’abord la granularité : ici, une ligne par adhérent.
• Agrégerez les emprunts et les retards séparément avant de joindre leurs résultats.
• Appliquez le filtre de retard dans la sous-requête qui calcule les retards.
• Utilisez les jointures externes et COALESCE pour conserver les adhérents sans activité.
• Rassemblez les titres concernés avec GROUP_CONCAT pour garder une ligne par adhérent.

La prochaine leçon portera sur la validation du rapport, notamment avec des cas de données qui permettent de vérifier ses résultats.