Discussion :

[Cleden]

Bonjour,

Ça fait quelques jours que je galère avec mon piètre niveau en mysql alors je viens vers vous en espérant que vous aurez une solution à mon problème.

J’ai trois tables:

Une table "produits" comportant les champs suivants:
--------------------------------
id_produit | designation |
---------------------------------
1 | pommes |
2 | poires |
3 | scoubidoubidou |
---------------------------------

une table "prix" comportant les champs suivants:
------------------------------------------------
id_prix | id_produit | prix | id_distributeur |
------------------------------------------------
1 | 2 | 3 | 1 |
2 | 3 | 2 | 3 |
3 | 1 | 2 | 2 |
------------------------------------------------

une table "distributeurs"
--------------------------------
id_distributeur | localisation |
--------------------------------
1 | 2 |
2 | 1 |
3 | 3 |
--------------------------------

Je souhaiterai afficher un tableau qui m’indique les derniers prix enregistrés par produit et par localisation

Voici ce que j’ai tenté (entres autres):

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT prix.id_distributeur, prix.produits, prix.prix, distributeurs.localisation, produits.id_produit
FROM prix 
INNER JOIN produits ON produits.id_produit = prix.id_produit
INNER JOIN (SELECT prix AS prix1 FROM prix INNER JOIN distributeurs ON (prix.id_distributeur = distributeur.id_distributeur AND distributeur.id_distributeur = 1) ORDER by id_prix DESC)) AS prixdistributeur1 
INNER JOIN (SELECT prix AS prix2 FROM prix INNER JOIN distributeurs ON (prix.id_distributeur = distributeur.id_distributeur AND distributeur.id_distributeur = 1) ORDER by id_prix DESC)) AS prixdistributeur2 
INNER JOIN (SELECT prix AS prix2 FROM prix INNER JOIN distributeurs ON (prix.id_distributeur = distributeur.id_distributeur AND distributeur.id_distributeur = 1) ORDER by id_prix DESC)) AS prixdistributeur3
GROUP BY prix.id_produit ";

Evidemment, ça ne marche pas du tout (on ne rigole pas ), est-ce que vous pouvez m’aiguiller ?

Loïc

[kevin254kl]

Salut, peux tu faire un export de la structure de tes tables afin qu'on puisse tester? Car à vue d'oeil c'est compliqué.
Plutôt que group by sur les id qui doit être unique j'aurais utilisé un order by desc puis un group by par localisation pour le regroupement, simple intuition .

[Cleden]

Voici structure et données de mes tables

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
-- --------------------------------------------------------
 
--
-- Structure de la table `distributeurs`
--
 
CREATE TABLE `distributeurs` (
  `id_distributeur` smallint(5) UNSIGNED NOT NULL,
  `nom_distributeur` varchar(100) NOT NULL,
  `categorie` smallint(6) NOT NULL,
  `localisation` smallint(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
--
-- Contenu de la table `distributeurs`
--
 
INSERT INTO `distributeurs` (`id_distributeur`, `nom_distributeur`, `categorie`, `localisation`) VALUES
(1, 'Sodifram Kaweni', 0, 1),
(2, 'Jumbo Score Hauts Vallons', 0, 1),
(3, 'Shopi Mamoudzou', 0, 1),
(4, 'Hyper Discount Kaweni', 0, 1),
(6, 'Sodicash Bandrélé', 2, 1);
 
-- --------------------------------------------------------
 
--
-- Structure de la table `prix`
--
 
CREATE TABLE `prix` (
  `id_prix` smallint(5) UNSIGNED NOT NULL,
  `id_produit` smallint(6) NOT NULL,
  `id_distributeur` smallint(6) NOT NULL,
  `localisation` int(11) NOT NULL,
  `prix` decimal(5,2) NOT NULL,
  `date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
--
-- Contenu de la table `prix`
--
 
INSERT INTO `prix` (`id_prix`, `id_produit`, `id_distributeur`, `localisation`, `prix`, `date`) VALUES
(1, 1, 2, 1, '10.00', '2018-12-02 00:00:00'),
(2, 2, 1, 1, '2.00', '2018-12-01 00:00:00'),
(24, 5, 6, 1, '3.95', '2018-11-23 00:00:00');
 
-- --------------------------------------------------------
 
--
-- Structure de la table `produits`
--
 
CREATE TABLE `produits` (
  `id_produit` smallint(5) UNSIGNED NOT NULL,
  `designation` varchar(100) NOT NULL,
  `poids` decimal(5,3) NOT NULL,
  `litrage` decimal(5,3) NOT NULL,
  `id_marque` smallint(6) NOT NULL,
  `id_categorie` smallint(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
--
-- Contenu de la table `produits`
--
 
INSERT INTO `produits` (`id_produit`, `designation`, `poids`, `litrage`, `id_marque`, `id_categorie`) VALUES
(1, 'Riz blanc', '5.000', '0.000', 1, 7),
(2, 'Yahourts Vanille', '0.000', '0.100', 2, 2),
(5, 'Frites Surgelées SteakHouse', '1.100', '0.000', 4, 3);
 
--
-- Index pour les tables exportées
--
 
--
-- Index pour la table `distributeurs`
--
ALTER TABLE `distributeurs`
  ADD PRIMARY KEY (`id_distributeur`);
 
--
-- Index pour la table `prix`
--
ALTER TABLE `prix`
  ADD PRIMARY KEY (`id_prix`);
 
--
-- Index pour la table `produits`
--
ALTER TABLE `produits`
  ADD PRIMARY KEY (`id_produit`);

[Watilin]

Evidement, ça ne marche pas du tout (on ne rigole pas ), est-ce que vous pouvez m’aiguiller ?

Je vois des erreurs de syntaxe dans ta requête (des parenthèses en trop). L’as-tu testée dans un outil PHPMyAdmin / Adminer / console SQL, ou bien via un PDO avec l’attribut PDO::ERRMODE_EXCEPTION ?

[Cleden]

Je teste au fur et à mesure mes requêtes dans phpmyadmin mais malheureusement ça ne résoud pas toutes mes lacunes. Toutefois, j’avance un peu dans ma réflexion et je suis arrivé à la requête suivante:

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
	IF (distributeurs.localisation = 1, prix.prix, 0) AS prixlocal,
	IF (distributeurs.localisation = 3, prix.prix, 0) AS prixailleurs,
	IF (distributeurs.localisation = 2, prix.prix, 0) AS prixencoreailleurs,
	prix.id_produit,
	prix.id_distributeur,
	prix.prix,
	produits.id_produit,
	produits.designation,
	distributeurs.localisation,
FROM prix
INNER JOIN produits ON produits.id_produit = prix.id_produit
INNER JOIN distributeurs ON prix.id_distributeur = distributeurs.id_distributeur
ORDER BY prix.id_prix DESC;

Avec cette requête, j’affiche tous les prix sur des lignes différentes mais si j’ajoute un GROUPBY id_produit, je "perd" certains prix. Existes-t-il un moyen d’utiliser le GROUPBY sans cette conséquence ?

[Watilin]

Un GROUP BY sans perdre de lignes, c’est un ORDER BY. Tu peux ordonner par plusieurs colonnes à la fois.

De plus, je remarque que tu as nommé tes colonnes de manière cohérente d’une table à l’autre, tu peux donc utiliser les jointures naturelles.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT *
FROM prix
NATURAL JOIN produits
NATURAL JOIN distributeurs
ORDER BY produits.id_produit ASC, distributeurs.localisation ASC, prix.date DESC;

Je ne sais pas si c’est exactement ce que tu veux, mais c’est pour te montrer que la solution est probablement plus simple que tu ne le penses.

[badaze]

Perso je trouve que ça manque d’enregistrements pour pouvoir tester. Il y en a tellement peu qu’on peut faire une requête qui fonctionnera mais qui ne marchera plus s’il y en a plus.
De plus c’est quoi le dernier prix ? Celui qui a un id plus élevé ou celui qui a une date plus récente ?

Le mieux serait de d’avoir, avec un nombre d’enregistrements réaliste, le résultat attendu.

[Cleden]

Je ne connaissais pas la jointure "naturelle" et c’est vrai que ça simplifie beaucoup

Avec le code suivant, j’arrive presque à ce que je veux

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
	IF (distributeurs.localisation = 1, prix.prix, 0) AS prixlocal,
	IF (distributeurs.localisation = 3, prix.prix, 0) AS prixailleurs,
	IF (distributeurs.localisation = 2, prix.prix, 0) AS prixencoreailleurs,
	prix.id_produit,
	prix.id_distributeur,
	prix.prix,
	produits.id_produit,
	produits.designation,
FROM produits
NATURAL JOIN prix
NATURAL JOIN distributeurs
ORDER BY prix.id_prix DESC;

Mais je ne veux qu’une seul ligne par produit avec les 3 prix en fonction de la localisation, et si je fais un GROUP BY id_produit, je perd certains prix.





J’ai ajouté quelques lignes à la table prix

-- Contenu de la table `prix`
--

INSERT INTO `prix` (`id_prix`, `id_produit`, `id_distributeur`, `prix`, `date`) VALUES
(1, 1, 2, '10.00', '2018-12-02 00:00:00'),
(2, 2, 1, '2.00', '2018-12-01 00:00:00'),
(3, 5, 6, '3.95', '2018-11-23 00:00:00'),
(4, 1, 10, '5.00', '2018-12-04 00:00:00'),
(5, 1, 11, '6.80', '2018-12-04 00:00:00'),
(6, 2, 4, '20.00', '2018-12-04 00:00:00'),
(7, 3, 10, '17.00', '2018-12-04 00:00:00’);

Merci beaucoup pour l’aide que vous m’avez déjà apporté.

[badaze]

Là il faut passer d’une structure de données verticale à une structure horizontale.
Si tu as trois types de localisations il faut faire 3 jointures sur la table.1 pour chaque type.

[badaze]

J’ai ajouté quelques lignes à la table prix


Merci beaucoup pour l’aide que vous m’avez déjà apporté.

Il n'y a pas la localisation dans les données.

[Watilin]

@badaze :

Il n'y a pas la localisation dans les données.

Probablement parce que Cleden l’a supprimée entre temps, elle n’était pas dans le message initial et elle fait double emploi avec celle de la table distributeurs.

@Cleden : en fait il nous faudrait surtout un jeu de données avec différentes localisations, car actuellement il n’y a que des 1.

Edit : j’ai fait ça, mais je ne suis pas sûr de moi.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
SELECT
    produits.id_produit,
    produits.designation,
    subquery1.localisation AS loc1,
    subquery1.prix AS prix1,
    subquery1.date AS date1,
    subquery2.localisation AS loc2,
    subquery2.prix AS prix2,
    subquery2.date AS date2,
    subquery3.localisation AS loc3,
    subquery3.prix AS prix3,
    subquery3.date AS date3
FROM produits
 
INNER JOIN (
    SELECT *
    FROM prix NATURAL JOIN distributeurs
    WHERE localisation = 1
) AS subquery1
    ON produits.id_produit = subquery1.id_produit
 
LEFT JOIN (
    SELECT *
    FROM prix NATURAL JOIN distributeurs
    WHERE localisation = 2
) AS subquery2
    ON produits.id_produit = subquery2.id_produit
 
LEFT JOIN (
    SELECT *
    FROM prix NATURAL JOIN distributeurs
    WHERE localisation = 3
) AS subquery3
    ON produits.id_produit = subquery3.id_produit
 
ORDER BY id_produit ASC, date1 DESC, date2 DESC, date3 DESC;

[…] pour te montrer que la solution est probablement plus simple que tu ne le penses.

Oui, je… Euh…

[Cleden]

On approche de la solution !



Sauf que cette syntaxe m’oblige à avoir un prix dans chaque localisation et, à défaut, créé une nouvelle ligne. Existe t-il on moyen d’attribuer la valeur NULL ou 0 lorsqu’un prix n’existe pas dans une localisation ?

Voici la table distributeurs avec quelques ajouts

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
-- Contenu de la table `distributeurs`
--
 
INSERT INTO `distributeurs` (`id_distributeur`, `nom_distributeur`, `categorie`, `localisation`) VALUES
(1, 'Sodifram Kaweni', 0, 1),
(2, 'Jumbo Score Hauts Vallons', 0, 1),
(3, 'Shopi Mamoudzou', 0, 1),
(4, 'Hyper Discount Kaweni', 0, 1),
(6, 'Sodicash Bandrélé', 2, 1),
(10, 'Carrefour sainte Suzanne', 0, 2),
(11, 'Leclerc Mérignac', 0, 3);

[MaitrePylos]

SQL n'est pas fait pour ce que tu demandes.
SQL est fait pour te donner ce que contient la db et non pas pour afficher un tableau tout préparé, c'est ton langage qui doit faire cela.

En PHP quand tu parses ton résultat tu peux lui dire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

if(f$tab['localisation'] == null){//affiche 0}

[Watilin]

Sauf que cette syntaxe m’oblige à avoir un prix dans chaque localisation

J’avais supposé que tu avais au moins un prix par produit. Mais j’ai fait une erreur en obligeant un tel prix à être dans le premier JOIN qui correpond à la localisation 1. Pour lever cette contrainte, il suffit de remplacer le INNER JOIN par LEFT JOIN.
La différence entre les deux, c’est que INNER JOIN est, comme son nom l’indique, une jointure interne, alors que LEFT JOIN est la syntaxe raccourcie de LEFT OUTER JOIN, où on comprend mieux que c’est une jointure externe, c’est-à-dire qu’elle autorise un côté de la jointure à ne pas avoir de données. En l’occurence, la jointure externe à gauche signifie « si une donnée est présente à gauche, on prend toute la ligne, peu importe ce qu’il y a du côté droit ».
Et tu l’auras compris, en cas d’absence de données, c’est NULL qui est renvoyé.

Existe t-il on moyen d’attribuer la valeur NULL ou 0 lorsqu’un prix n’existe pas dans une localisation ?

La jointure externe renvoie déjà des NULL. Si tu ne les vois pas, c’est probablement la faute à PHP qui ne sait pas les afficher correctement. Utilise is_null :

Code PHP :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
$bidule = null;
echo is_null($bidule) ? 'null' : $bidule;

Sinon, tu peux utiliser COALESCE pour fournir une valeur par défaut. Exemple :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT COALESCE(prix, 0) FROM ...

[Cleden]

Merci beaucoup, avec GROUP BY produits.id_produit et tout est ok

[SQLpro]

Un GROUP BY sans perdre de lignes, c’est un ORDER BY. Tu peux ordonner par plusieurs colonnes à la fois.

STupide et faux. Un GROUP By ne fait pas de tri !

De plus, je remarque que tu as nommé tes colonnes de manière cohérente d’une table à l’autre, tu peux donc utiliser les jointures naturelles.

C'est la pire merde qui soit et même la norme SQL a abandonné la jointure naturelle car trop dangereuse !

[Watilin]

STupide et faux. Un GROUP By ne fait pas de tri !

Ok, je reconnais que j’ai parlé sans maîtriser le sujet, mea culpa.

C'est la pire merde qui soit et même la norme SQL a abandonné la jointure naturelle car trop dangereuse !

Pour moi c’est juste un raccourci de syntaxe, qui plus est sur des noms de colonnes choisis spécifiquement pour marcher, donc qui encourage à concevoir ses tables avec soin. Mais vu comme ça a l’air de bien t’agacer, j’aimerais comprendre les inconvénients de cette forme de jointure, si tu veux bien prendre le temps

[badaze]

J’hasarderais que tout ce qui n’est pas énoncé n’est pas maîtrisé.

[SQLpro]

Ok, je reconnais que j’ai parlé sans maîtriser le sujet, mea culpa.


Pour moi c’est juste un raccourci de syntaxe, qui plus est sur des noms de colonnes choisis spécifiquement pour marcher, donc qui encourage à concevoir ses tables avec soin. Mais vu comme ça a l’air de bien t’agacer, j’aimerais comprendre les inconvénients de cette forme de jointure, si tu veux bien prendre le temps

Simple....

Imaginons les tables suivantes :

• Personne (clef PRS_ID
• Adresse (relatives aux personnes, avec FK PRS_ID)
• Telephone (relatives aux personnes, avec FK PRS_ID)
• Email (relatives aux personnes, avec FK PRS_ID)

Et donc la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT *
FROM   Personne AS P
       NATURAL JOIN Adresse AS A
       NATURAL JOIN Telephone AS T
       NATURAL JOIN Email AS E

Le principe de la jointure naturelle est de joindre toutes les colonnes de mêmes noms avec les tables exprimées auparavant.
Ce qui conduit à la construction équivalente suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT *
FROM   Personne AS P
       JOIN Adresse AS A    ON P.PRS_ID = A.PRS_ID
       JOIN Telephone AS T  ON P.PRS_ID = T.PRS_ID AND P.PRS_ID = T.PRS_ID
       JOIN Email AS E      ON P.PRS_ID = T.PRS_ID AND P.PRS_ID = T.PRS_ID AND P.PRS_ID = E.PRS_ID

Ce qui conduit donc à des prédicats redondants et des jointures "triangulaires" (je devrait dire n-angulaires) et donc les performances s'écroulent !

De plus en cas d'évolution de la structure de la base, le danger est immense. Imaginez ce qui se passerais si le DSI vous demande de rajouter une colonne OBSERVATION dans toutes les tables.

Pour information, la norme SQL considère le NATURAL JOIN comme dangereux et a été retiré de la norme... Bien évidemment MySQmerde a sauté les pieds joint dans la merde !!!!!

A +

[SQLpro]

J’hasarderais que tout ce qui n’est pas énoncé n’est pas maîtrisé.

effectivement et avec mon post ci-avant CQFD !

A +