Cours MariaDB / Joindre plusieurs tables sans doubler les totaux
Pour éviter les totaux doublés après plusieurs JOIN, identifiez d’abord ce que représente une ligne du résultat. Relier une commande à ses articles répète les frais de livraison pour chaque article : regroupez les articles par commande avant de les relier aux montants facturés une seule fois par commande.
Distinguer client, commande, article et produit
Un résultat qui associe le nom du client, la commande, le produit et la quantité peut utiliser quatre tables. Sa ligne finale représente un article d’une commande, pas la commande entière. Cette unité de lecture, parfois appelée granularité, détermine les valeurs que vous pouvez additionner directement.
| Valeur | Unité d’origine | Après la jointure avec les articles |
|---|---|---|
| customer_name | Client | Répété pour les articles des commandes du client |
| shipping_fee | Commande | Répété pour chaque article de cette commande |
| quantity × unit_price | Article de commande | Montant propre à la ligne, additionnable à ce niveau |
| price du catalogue | Produit actuel | Ne remplace pas le prix historique enregistré dans l’article |
La présence de plusieurs lignes pour une commande n’est pas forcément une erreur de jointure : c’est une conséquence normale de la relation un-à-plusieurs. L’erreur apparaît quand vous additionnez une valeur de commande comme si elle appartenait à chaque article. Le choix entre conserver seulement les correspondances ou garder aussi les absences relève d’une autre question, présentée dans le cours sur LEFT JOIN.
Préparer les prix historiques et les frais de livraison
Connectez-vous au serveur d’apprentissage avec course_user et utilisez la base shopcourse. Le compte doit disposer des droits de création, d’insertion, de lecture et de nettoyage des objets d’exercice. Le guide du client MariaDB et des fichiers SQL UTF-8 détaille la connexion et l’exécution d’un fichier.
- Dans le client MariaDB, exécutez
SELECT VERSION(), DATABASE();puisSHOW TABLES;. Vérifiez le serveur et la base avant de créer les tables. - Les noms
c28_customers,c28_products,c28_ordersetc28_itemssont propres à cet exercice. S’ils existent, inspectez leurs définitions et leurs données. Ne supprimez pas des objets dont le contenu doit être conservé. - Enregistrez tout le bloc suivant dans un fichier UTF-8, par exemple
C:/mariadb-lab/c28-multiple-joins.sql, en créant le dossier si nécessaire. ExécutezSOURCE C:/mariadb-lab/c28-multiple-joins.sql;dans le client, en adaptant le chemin réel. Les instructions SQL ne se saisissent pas directement à l’invite du système.
USE shopcourse;
CREATE TABLE c28_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c28_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c28_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c28_order_customer FOREIGN KEY (customer_id)
REFERENCES c28_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c28_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c28_item_order FOREIGN KEY (order_id)
REFERENCES c28_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c28_item_product FOREIGN KEY (product_id)
REFERENCES c28_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c28_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c28_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c28_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c28_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
Ce jeu autonome contient quatre clients, quatre produits, quatre commandes et six articles de commande. Les chaînes coréennes restent identiques dans les traductions pour permettre de reproduire les résultats. Les produits 1 à 4 sont un carnet, un stylo, une tasse et une lampe. Les villes des clients 1 et 3 sont 서울 (Séoul), celle du client 2 부산 (Busan), celle du client 4 대전 (Daejeon). Conservez les textes UTF-8 et utilisez les identifiants pour suivre les calculs.
Les commandes payées, de statut paid, sont 101, 102 et 103. Elles possèdent respectivement deux, un et deux articles, soit cinq lignes à examiner. La commande 104 est annulée et sera exclue. Chaque commande a des frais de livraison de 1000.00 ; cette égalité de valeurs rend visible le piège de SUM(DISTINCT ...).
Relier les quatre tables avec les bonnes clés
Exécutez cette requête dans shopcourse. Chaque condition ON exprime une relation : commande-client, commande-article, puis article-produit.
SELECT o.order_id,c.customer_name,p.product_name,i.quantity,i.unit_price,
i.quantity*i.unit_price AS line_total
FROM c28_orders o JOIN c28_customers c ON c.customer_id=o.customer_id
JOIN c28_items i ON i.order_id=o.order_id
JOIN c28_products p ON p.product_id=i.product_id
WHERE o.status='paid' ORDER BY o.order_id,i.product_id;
| order_id | customer_name | product_name | quantity | unit_price | line_total |
|---|---|---|---|---|---|
| 101 | 김민수 | 노트 | 2 | 3500.00 | 7000.00 |
| 101 | 김민수 | 볼펜 | 3 | 1500.00 | 4500.00 |
| 102 | 김민수 | 머그컵 | 1 | 8000.00 | 8000.00 |
| 103 | 이서연 | 노트 | 1 | 3500.00 | 3500.00 |
| 103 | 이서연 | 머그컵 | 2 | 8000.00 | 16000.00 |
Vous devez obtenir cinq lignes, triées explicitement par commande et produit. Pour la commande 101, les marchandises valent 7000 + 4500 = 11500. La commande 102 vaut 8000 et la commande 103 3500 + 16000 = 19500. Le total des marchandises payées est donc 39000.00, hors livraison.
Le nom du produit vient du catalogue p, mais le montant utilise le prix historique i.unit_price. Le prix courant p.price peut changer après l’achat. Le remplacer dans le calcul modifierait rétrospectivement le montant du rapport, alors que les articles de la commande n’ont pas changé.
Repérer les frais de commande additionnés plusieurs fois
Cette requête montre le problème sans modifier les données :
SELECT COUNT(*) AS joined_rows,COUNT(DISTINCT o.order_id) AS orders_count,
SUM(o.shipping_fee) AS duplicated_shipping,SUM(i.quantity*i.unit_price) AS sales
FROM c28_orders o JOIN c28_items i ON i.order_id=o.order_id
WHERE o.status='paid';
| Colonne | Résultat | Interprétation |
|---|---|---|
| joined_rows | 5 | Cinq articles de commandes payées |
| orders_count | 3 | Trois identifiants de commande différents |
| duplicated_shipping | 5000.00 | Frais répétés cinq fois, total incorrect |
| sales | 39000.00 | Somme des montants propres à chaque article |
La livraison devrait totaliser 3000.00, soit trois commandes à 1000. La jointure répète deux fois les frais de la commande 101 et deux fois ceux de la commande 103 ; elle ajoute donc 2000.00 de trop. COUNT(DISTINCT o.order_id) corrige le nombre de commandes, mais ne change pas le jeu de lignes reçu par les autres fonctions d’agrégation.
Remplacer la somme par SUM(DISTINCT o.shipping_fee) ne convient pas : les trois commandes partagent la même valeur 1000, qui ne serait additionnée qu’une fois. Vous obtiendriez 1000.00, pas 3000. La documentation de SUM et de son option DISTINCT porte sur les valeurs différentes de l’expression, pas sur l’identité des commandes. Dédupliquer les montants et compter chaque commande une fois sont deux opérations distinctes.
Résumer les articles par commande avant la jointure
La sous-requête suivante calcule une seule ligne par order_id. L’alias d désigne ce résultat intermédiaire, appelé table dérivée, que l’on relie ensuite aux commandes :
SELECT COUNT(*) AS orders_count,SUM(o.shipping_fee) AS shipping_total,
SUM(d.goods_total) AS goods_total
FROM c28_orders o
JOIN (SELECT order_id,SUM(quantity*unit_price) AS goods_total
FROM c28_items GROUP BY order_id) d ON d.order_id=o.order_id
WHERE o.status='paid';
Le résultat attendu est orders_count = 3, shipping_total = 3000.00 et goods_total = 39000.00. La sous-requête possède un total de marchandises pour chaque commande, grâce à GROUP BY order_id. La jointure ne répète donc plus les frais en fonction du nombre d’articles.
La sous-requête résume ici tous les statuts ; le filtre extérieur sélectionne ensuite les commandes payées. C’est correct parce que les frais et le statut appartiennent à la commande, et que les totaux intermédiaires sont reliés par son identifiant. La référence JOIN de MariaDB décrit l’utilisation de sous-requêtes nommées dans les tables de départ.
Cette jointure interne cible les commandes payées ayant au moins un article. Si le rapport doit aussi conserver les commandes sans article, il faut définir cette population avec une jointure externe et décider comment représenter le montant manquant, par exemple zéro avec COALESCE. L’absence d’articles et un total égal à zéro ne doivent pas être confondus sans règle métier. Sur une sélection vide, SUM() renvoie NULL ; COUNT(*) donne zéro.
Contrôler la multiplicité et refaire l’essai
Ajoutez les tables une à une en affichant les clés. Comptez les lignes, les identifiants de commande distincts et les montants à chaque étape. Une hausse attendue lors du passage aux articles est normale ; une hausse sans relation avec les données peut révéler une condition manquante ou une clé de correspondance non unique.
Pour une comparaison reproductible, ajoutez uniquement un article à la commande 102 avec INSERT INTO shopcourse.c28_items VALUES (102,4,1,25000);, puis relancez les deux requêtes d’agrégation. Le nombre de lignes jointes devient 6 et les marchandises 64000.00. La somme directe des frais devient incorrectement 6000.00, tandis que la requête résumée conserve trois commandes et 3000.00 de livraison.
Rétablissez ensuite le jeu initial avec DELETE FROM shopcourse.c28_items WHERE order_id=102 AND product_id=4;, puis vérifiez les résultats 5 / 3 / 5000.00 / 39000.00 pour la requête de diagnostic et 3 / 3000.00 / 39000.00 pour la requête corrigée. Faites cette manipulation uniquement sur les tables d’exercice dont vous avez vérifié le contenu.
Si vous reliez deux collections de détails indépendantes à une même commande, leurs lignes peuvent se multiplier entre elles : deux articles et trois autres détails peuvent produire six combinaisons. Résumez chaque collection au niveau de la commande avant de combiner leurs mesures. Un DISTINCT final ne remplace pas cette définition du niveau de calcul.
Pour recommencer depuis zéro, inspectez la base, les définitions et les lignes, puis sauvegardez les données à garder. Le bloc suivant enlève uniquement les objets c28_ de cet exercice, des enfants vers leurs parents. La documentation de DROP TABLE rappelle la suppression des structures et des données ; un ROLLBACK ordinaire ne les restaure pas.
DROP TABLE shopcourse.c28_items;
DROP TABLE shopcourse.c28_orders;
DROP TABLE shopcourse.c28_products;
DROP TABLE shopcourse.c28_customers;
Réexécutez le bloc de préparation complet, puis les trois lectures. Les cinq lignes d’articles et les totaux initiaux doivent réapparaître. Les tables d’autres exercices, notamment les préfixes c27_, et une éventuelle table générale products sont exclues de ce nettoyage.




