Créer une vue complexe à partir d'une base de données

Version imprimable

1 pièce(s) jointe(s)

Bonjour,
voici une partie de la base de données pour laquelle je souhaite créer une vue pour un site web qui affiche les produits et leurs notes
Pièce jointe 142221
Code:

1 2 3 4 5 6 7 8 9 categorie (id_cat, nom_cat) typ (id_typ, nom_typ, #id_cat) marque (id_marq, nom_marq) produit (id_prod, nom_prod, img_prod, #id_marq) internaute (id_int, pseudo_int, pass_int) appartient (id_cat, id_marq) avoir (id_prod, id_typ) avis (id_prod, id_int, note)
Ce que je veux obtenir est une vue qui respecte la structure suivante :
Code:

1 2 3 4 5 vue(id_prod, nom_prod, img_prod, id_marq, nom_marq, id_typ, nom_typ, moyenne_note) à condition que id_cat=2 moyenne_note=avg(avis.note) par produit
regrouper la sélection de moyenne_note en même temps que d'afficher les produits me bloque,
pouvez-vous m'aider là-dessus ?

Merci à tout ceux qui veulent aider :ccool:
amine

08/04/2014, 09h12
Exia_

Bonjour,

Montrez nous la requête que vous avez déjà fait pour que l'on puisse vous aiguiller.

Il faudra que vous utilisiez la clause GROUP BY pour connaitre la moyenne des notes.

Comme dit le camarade, avec un Group By (bien placé) ça peut passer...

Quelque chose comme ça (à vérifier !) :

Code:

1
2
3
4
5
6
SELECT x.IdProd, AVG(Note) AS Moyenne  
FROM   PRODUIT AS x JOIN APPARTIENT AS y ON x.IdMarq = y.IdMarq
                    JOIN AVIS AS z ON x.IdProd = z.IdProd 
WHERE  y.IdCat = 'cat01'
GROUP BY IdProd  
;

Plus complet :

Code:

1
2
3
4
5
6
7
8
9
10
CREATE VIEW VUE (id_prod, nom_prod, img_prod, id_marq, nom_marq, id_typ, nom_typ, moyenne_note) 
AS
SELECT x.IdProd, x.NomProd, x.Imgprod, y.IdMarq, NomMarq, u.IdTyp, u.NomTyp, AVG(Note) AS Moyenne  
FROM   PRODUIT AS x JOIN APPARTIENT AS y ON x.IdMarq = y.IdMarq
                    JOIN AVIS AS z ON x.IdProd = z.IdProd 
                    JOIN MARQUE AS t ON y.IdMarq = t.IdMarq 
                    JOIN TYPEE AS u ON y.IdCat = u.IdCat
WHERE  y.IdCat = 'cat01'
GROUP BY IdProd 
;

Citation:
Envoyé par fsmrel
Plus complet :
Code:

1 2 3 4 5 6 7 8 9 10 CREATE VIEW VUE (id_prod, nom_prod, img_prod, id_marq, nom_marq, id_typ, nom_typ, moyenne_note) AS SELECT x.IdProd, x.NomProd, x.Imgprod, y.IdMarq, NomMarq, u.IdTyp, u.NomTyp, AVG(Note) AS Moyenne FROM PRODUIT AS x JOIN APPARTIENT AS y ON x.IdMarq = y.IdMarq JOIN AVIS AS z ON x.IdProd = z.IdProd JOIN MARQUE AS t ON y.IdMarq = t.IdMarq JOIN TYPEE AS u ON y.IdCat = u.IdCat WHERE y.IdCat = 'cat01' GROUP BY IdProd ;
Merci à fsmrel et à Exia93 de m'avoir mis sur la ligne,
pour la solution proposée, elle ne m'affiche que les produits ayant une note, aussi, elle m'affiche le même type pour tous les produits !

J'ai pu rectifier ça et ça marche super, voila la solution :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 CREATE VIEW VUE (id_prod, nom_prod, img_prod, id_marq, nom_marq, id_typ, nom_typ, moyenne_note) AS SELECT x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ, AVG(Note) AS Moyenne FROM PRODUIT AS x LEFT JOIN APPARTIENT AS y ON x.IdMarq = y.IdMarq LEFT JOIN AVIS AS z ON x.Id_Prod = z.Id_Prod LEFT JOIN MARQUE AS t ON y.Id_Marq = t.Id_Marq LEFT JOIN AVOIR AS f ON f.id_prod=x.id_prod LEFT JOIN TYP AS u ON y.Id_typ = u.Id_typ WHERE y.IdCat = 'cat01' GROUP BY IdProd ;
Entre temps, je me suis aperçus que je dois d'abord valider les notes attribuées par les internautes, donc j'ai rajouté la propriété validation à l'association avis qui prend pour valeur soit "oui" ou "non";
donc, ma base de données est devenue comme ça :
Code:

1 2 3 4 5 6 7 8 9 categorie (id_cat, nom_cat) typ (id_typ, nom_typ, #id_cat) marque (id_marq, nom_marq) produit (id_prod, nom_prod, img_prod, #id_marq) internaute (id_int, pseudo_int, pass_int) appartient (id_cat, id_marq) avoir (id_prod, id_typ) avis (id_prod, id_int, note, validation)
De ce fait, la moyenne des notes est calculée à partir des notes pour lesquelles le champ validation vaut "oui",
et quand j'ajoute la condition z.validation='oui' dans la clause WHERE, j'obtiens une vue qui n'affiche que les produits ayant des notes et pas les autres !
Comment faire pour obtenir la bonne vue ?
amine

Bonjour,

La requête n'est pas conforme à la norme SQL, comme expliqué dans le cours de SQLPro sur la clause GROUP BY :

Citation:

De plus, toutes les colonnes représentées hors des calculs d'agrégation doivent figurer dans la clause GROUP BY.

Sans ça les colonnes non soumises à une fonction de regroupement ne se trouvant pas dans le GROUP BY auront une valeur aléatoire dans l'ensemble des valeurs disponibles pour ce regroupement.

Votre requête ré-écrite :

Code:

1
2
3
4
5
6
7
8
9
10
CREATE VIEW VUE (id_prod, nom_prod, img_prod, id_marq, nom_marq, id_typ, nom_typ, moyenne_note) 
AS
SELECT x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ, AVG(Note) AS Moyenne  
FROM   PRODUIT AS x LEFT JOIN APPARTIENT AS y ON x.IdMarq = y.IdMarq
                    LEFT JOIN AVIS AS z ON x.Id_Prod = z.Id_Prod 
                    LEFT JOIN MARQUE AS t ON y.Id_Marq = t.Id_Marq 
                    LEFT JOIN AVOIR AS f ON f.id_prod=x.id_prod
                    LEFT JOIN TYP AS u ON y.Id_typ = u.Id_typ
WHERE  y.IdCat = 'cat01'
GROUP BY x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ

Pour votre nouvelle colonne "validation", préférez un booléen ou un entier court (bit, Booleen, entier court), car la comparaison d'une chaîne de caractères avec une autre est plus coûteuse qu'un simple booléen ou un entier court.

Votre nouvelle requête :

Code:

1
2
3
4
5
6
7
8
9
10
CREATE VIEW VUE (id_prod, nom_prod, img_prod, id_marq, nom_marq, id_typ, nom_typ, moyenne_note) 
AS
SELECT x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ, AVG(Note) AS Moyenne  
FROM   PRODUIT AS x LEFT JOIN APPARTIENT AS y ON x.IdMarq = y.IdMarq
                    LEFT JOIN AVIS AS z ON x.Id_Prod = z.Id_Prod AND z.validation = 'oui' -- à remplacer par la nouvelle valeur
                    LEFT JOIN MARQUE AS t ON y.Id_Marq = t.Id_Marq 
                    LEFT JOIN AVOIR AS f ON f.id_prod=x.id_prod
                    LEFT JOIN TYP AS u ON y.Id_typ = u.Id_typ
WHERE  y.IdCat = 'cat01'
GROUP BY x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ

09/04/2014, 12h01
jamdinhe

Merci Exia93 pour ces explications et améliorations,
malheureusement la requête n'affiche toujours que les produits ayant des notes,

car dans ma base de données la table avis n'est remplie que si un internaute donne une note au produit, sinon un produit peut ne pas avoir reçu de note (les cardinalités du MCD le montrent bien);

Où peut se trouver la solution ?:(

Merci encore à tous ceux qui veulent aider.
amine

Après avoir revu votre MCD, il vous suffit au lieu de faire une jointure sur toute la table Avis de faire une jointure seulement sur les enregistrements validés :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE VIEW VUE (id_prod, nom_prod, img_prod, id_marq, nom_marq, id_typ, nom_typ, moyenne_note) 
AS
SELECT x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ, AVG(Note) AS Moyenne  
FROM   PRODUIT AS x LEFT JOIN APPARTIENT AS y ON x.IdMarq = y.IdMarq
                    LEFT JOIN (
                            SELECT av.id_prod, av.id_int, av.note
                            FROM AVIS av
                            WHERE av.validation = 'oui'
 
                    ) AS z ON x.Id_Prod = z.Id_Prod
                    LEFT JOIN MARQUE AS t ON y.Id_Marq = t.Id_Marq 
                    LEFT JOIN AVOIR AS f ON f.id_prod=x.id_prod
                    LEFT JOIN TYP AS u ON y.Id_typ = u.Id_typ
WHERE  y.IdCat = 'cat01'
GROUP BY x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ

Citation:

Envoyé par Exia93

Après avoir revu votre MCD ils vous suffit au lieu de faire une jointure sur toute la table Avis de faire une jointure seulement sur les enregistrements validés :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE VIEW VUE (id_prod, nom_prod, img_prod, id_marq, nom_marq, id_typ, nom_typ, moyenne_note) 
AS
SELECT x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ, AVG(Note) AS Moyenne  
FROM   PRODUIT AS x LEFT JOIN APPARTIENT AS y ON x.IdMarq = y.IdMarq
                    LEFT JOIN (
                            SELECT av.id_prod, av.id_int, av.note
                            FROM AVIS av
                            WHERE av.validation = 'oui'
 
                    ) AS z ON x.Id_Prod = z.Id_Prod
                    LEFT JOIN MARQUE AS t ON y.Id_Marq = t.Id_Marq 
                    LEFT JOIN AVOIR AS f ON f.id_prod=x.id_prod
                    LEFT JOIN TYP AS u ON y.Id_typ = u.Id_typ
WHERE  y.IdCat = 'cat01'
GROUP BY x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ

Merci pour la réponse,
:?:?:? ça ne crée pas la vue:?:?:?

Je ne comprends pas pourquoi, lorsque je fais juste

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ, AVG(Note) AS Moyenne  
FROM   PRODUIT AS x LEFT JOIN APPARTIENT AS y ON x.IdMarq = y.IdMarq
                    LEFT JOIN (
                            SELECT av.id_prod, av.id_int, av.note
                            FROM AVIS av
                            WHERE av.validation = 'oui'
 
                    ) AS z ON x.Id_Prod = z.Id_Prod
                    LEFT JOIN MARQUE AS t ON y.Id_Marq = t.Id_Marq 
                    LEFT JOIN AVOIR AS f ON f.id_prod=x.id_prod
                    LEFT JOIN TYP AS u ON y.Id_typ = u.Id_typ
WHERE  y.IdCat = 'cat01'
GROUP BY x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ

ça affiche tout comme je le voulais, mais lorsque je rajoute CREATE VIEW ......, il me sort l'erreur suivante

Citation:

#1349 - View's SELECT contains a subquery in the FROM clause

il n'a pas l'air d’accepter le select qui est dans la clause WHERE :?:?:?

Merci, mais je sens que la solution est juste là !! mais où ?
amine

Après une petite recherche les sous-requêtes ne sont pas acceptées dans la création d'une vue dans mysql --'

Une des solutions est de créer une vue pour afficher seulement les avis validés et d'utiliser cette vue dans la nouvelle vue :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE VIEW AvisValide (id_prod, id_int, note)
AS 
SELECT av.id_prod, av.id_int, av.note
FROM AVIS av
WHERE av.validation = 'oui';
 
CREATE VIEW VUE (id_prod, nom_prod, img_prod, id_marq, nom_marq, id_typ, nom_typ, moyenne_note) 
AS
SELECT x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ, AVG(Note) AS Moyenne  
FROM   PRODUIT AS x LEFT JOIN APPARTIENT AS y ON x.IdMarq = y.IdMarq
                    LEFT JOIN AvisValide AS z ON x.Id_Prod = z.id_Prod
                    LEFT JOIN MARQUE AS t ON y.Id_Marq = t.Id_Marq 
                    LEFT JOIN AVOIR AS f ON f.id_prod=x.id_prod
                    LEFT JOIN TYP AS u ON y.Id_typ = u.Id_typ
WHERE  y.IdCat = 'cat01'
GROUP BY x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ

Citation:
Envoyé par Exia93
Après une petite recherche les sous-requêtes ne sont pas acceptées dans la création d'une vue dans mysql --'

Une des solutions est de créer une vue pour afficher seulement les avis validés et d'utiliser cette vue dans la nouvelle vue :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE VIEW AvisValide (id_prod, id_int, note) AS SELECT av.id_prod, av.id_int, av.note FROM AVIS av WHERE av.validation = 'oui'; CREATE VIEW VUE (id_prod, nom_prod, img_prod, id_marq, nom_marq, id_typ, nom_typ, moyenne_note) AS SELECT x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ, AVG(Note) AS Moyenne FROM PRODUIT AS x LEFT JOIN APPARTIENT AS y ON x.IdMarq = y.IdMarq LEFT JOIN AvisValide AS z ON x.Id_Prod = z.id_Prod LEFT JOIN MARQUE AS t ON y.Id_Marq = t.Id_Marq LEFT JOIN AVOIR AS f ON f.id_prod=x.id_prod LEFT JOIN TYP AS u ON y.Id_typ = u.Id_typ WHERE y.IdCat = 'cat01' GROUP BY x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ
Merci Exia93, je prends cette solution comme une alternative s'il n'y a personne d'autre pour proposer une solution directe.

J'espère que ça va aider d'autres développeurs.

Sinon, vous pensez quoi du MCD ? Vous le trouvez normalisé ?

Car j'ai beaucoup réfléchi pour éviter qu'il y ait une sorte de cercle fermé par les jointures, mais je n'ai pas trouvé de meilleure solution.

Enfin, j'attends s'il y a encore des solutions qui ce présentent pour le problème de la vue, sinon je mettrais la question à RESOLU

merci encore à tous ceux qui veulent aider :ccool:
amine

09/04/2014, 17h12
fsmrel

Bonsoir,

Citation:

Envoyé par jamdinhe

vous pensez quoi de l'MCD ? vous le trouver normalisé ?

Sans les règles de gestion des données, on ne peut rien affirmer. Cela dit, sémantique mise à part, au vu des dépendances fonctionnelles inférées des clés des tables, votre MLD (et pas MCD) est au moins en BCNF (forme normale de Boyce-Codd).

Toutefois,

— La table CATEGORIE et la table APPARTIENT devraient n’en faire qu’une.

— Pour une marque donnée selon que l’on suive le chemin MARQUE, CATEGORIE, TYP, AVOIR ou le chemin MARQUE, PRODUIT, AVOIR, on peut obtenir des résultats différents. Est-ce normal ?

Citation:

Envoyé par jamdinhe

elle m'affiche le même type pour tous les produits !

Effectivement, il manque la colonne Id_typ dans le GROUP BY. Cela dit, au vu de la structure de vos tables (MLD), il n’y a que le produit qui soit partie prenante dans le calcul de la moyenne des notes, donc la moyenne affichée devrait être la même pour les différents types associés à un produit.

Bon, complétons, allons-y pour un GROUP BY x.Id_Prod, u.Id_Typ.

Citation:

Envoyé par Exia93

La requête n'est pas conforme à la norme SQL

Il est vrai que selon la norme SQL:1999, chaque nom de colonne figurant dans la clause GROUP BY doit aussi figurer dans la clause SELECT (à moins de figurer en tant qu’argument dans une fonction d’agrégation). En l’occurrence, je fais référence à l’ouvrage de Peter Gulutzan et Truly Pelzer, SQL-99 Complete, Really, au paragraphe « Rules for Grouping Columns » aux pages 634, 635, cité aussi ici (« the Single-Value Rule » :

You must follow “The Single-Value Rule” — every Column named in the select list must also be a grouping Column, unless it is an argument for one of the set functions.

Je note en passant que les fonctions auxquelles il est fait allusion peuvent retourner des sacs plutôt de des ensembles, mais bon. Quoi qu’il en soit, quelqu’un sait-il où on en est avec la version SQL:2011 ?
A cette occasion, je fais observer qu’à l’ensemble E des noms des attributs figurant dans la clause SELECT est associé l’ensemble F de dépendances fonctionnelles suivantes, inférables des clés des tables :

F = {Id_Prod -> Nom_Prod, Id_Prod -> Img_Prod, id_Prod -> Id_Marq, Id_Marq -> Nom_Marq}.

Autrement dit, “The Single-Value Rule” est trop simple et devrait être aménagée pour tenir compte des dépendances fonctionnelles. Cela a pour conséquence qu’on ne devrait pas avoir à nommer les attributs jouant le rôle de dépendant dans les dépendances fonctionnelles ci-dessus : passage au rasoir d’Ockham, ce dont manifestement MySQL a tenu compte (à ceci près qu’il aurait dû me signaler une erreur pour avoir omis l’attribut Id_Typ dans la clause SELECT puisqu’absent de l’ensemble F :D).

Pouvez-vous fournir vos règles de gestion, notamment en ce qui concerne les relations entre les types et les catégories ?
09/04/2014, 18h00
jamdinhe

1 pièce(s) jointe(s)

Merci fsmrel pour les explications, j'avoue que je me suis un peu perdu dedans surtout quand l'anglais s'est mis à expliquer les détails, mais ça va j'arrive à ramer encore ;) :ptdr:

En ce qui est des règles de gestion, les voici :

Citation:

tout internaute a accès à un ensemble de produits, il peut y mettre son avis par une note, cette dernière n'est prise en considération que lorsque le webmaster la valide;
les produits sont affichés selon leur catégorie, pour la catégorie informatique par exemple, nous avons une liste de produits que chacun d'entre eux a un ou plusieurs types (exp: produit : Écran samsung, type:{écran PC, TV LED}) le type à son tour ne peut être que dans une seule catégorie (exp: écran PC -> informatique, TV LED -> électroménager);
en même temps, le produit ne peut avoir qu'une seule marque et une marque appartient à une ou plusieurs catégories, ensuite la catégorie peut recevoir plusieurs marques,

quand le webmaster ajoute un nouveau produit, il faut qu'il y ait une liste prédéfinie de toutes les marques appartenant à la catégorie pour laquelle il souhaite effectuer l'ajout et aussi tous les types relatifs à cette dernière; bien entendu, si la marque ou le type n'existent pas, le webmaster doit pouvoir les ajouter en relation avec la catégorie.

sinon, pour le MCD, ci-joint une image de la dernière version qui est relative au dernier MLD posté dans le forum
Pièce jointe 142531

merci encore à tous ceux qui veulent aider
amine
09/04/2014, 18h20
aieeeuuuuu

Bonjour fsmrel

Citation:

Envoyé par fsmrel

Quoi qu’il en soit, quelqu’un sait-il où on en est avec la version SQL:2011 ?

Il me semble (mais ça reste a confirmer, si je trouve des sources je les posterai) qu'elle a pris en compte par anticipation votre remarque :) :

Citation:

Autrement dit, “The Single-Value Rule” est trop simple et devrait être aménagée pour tenir compte des dépendances fonctionnelles. Cela a pour conséquence qu’on ne devrait pas avoir à nommer les attributs jouant le rôle de dépendant dans les dépendances fonctionnelles ci-dessus : passage au rasoir d’Ockham, ce dont manifestement MySQL a tenu compte (à ceci près qu’il aurait dû me signaler une erreur pour avoir omis l’attribut Id_Typ dans la clause SELECT puisqu’absent de l’ensemble F :D).

Mais à ma connaissance aucun SGBD n'implémente cette subtilité.
Pour le cas particulier de MySql, je dirai plutôt qu'il n'implémente rien à ce niveau, et laisse libre le contenu du GROUP BY, ce qui peut être dangereux : si le "sac" contient plusieurs billes différentes, MySQL renverra la première qu'il attrapera !
09/04/2014, 20h00
fsmrel

Bonsoir,

Citation:

Envoyé par jamdinhe

les produits sont affichés selon leur catégorie

Merci pour les règles de gestion et pour votre MCD. Attention, selon votre MLD, il existe une dépendance fonctionnelle id cat -> id_marq (une catégorie fait référence à une seule marque), mais vu votre MCD et vos exemples, il est à aménager. Effectivement, que l’électro-ménager fasse référence à une seule marque paraît suspect. :D

A cette remarque près, les modèles sont corrects.

Citation:

Envoyé par aieeeuuuuu

Pour le cas particulier de MySql, je dirai plutôt qu'il n'implémente rien à ce niveau, et laisse libre le contenu du GROUP BY, ce qui peut être dangereux : si le "sac" contient plusieurs billes différentes, MySQL renverra la première qu'il attrapera !

D’accord, aieeeuuuuu le saaaageeuuuu ^^, je viens de m’en rendre compte :whistle:...
09/04/2014, 22h02
jamdinhe

Merci fsmrel pour la remarque, au fait c'était moi qui m'était trompé quand j'avais repris le MLD sur le forum, je viens de le corriger, donc c'est bon tout rentre dans l'ordre.

Sinon, pour la vue, apparemment il n'y a pas d'autres solutions à part celle qu'a donné Exia93 ?

Si c'est le cas, je mets la discussion sur Résolu.

Merci encore à tout le monde, j'espère que d'autres développeurs vont bénéficier de ces informations :ccool:
amine

Si le SGBD rouspète et refuse de créer la vue (c'est MySQL ?), vous pouvez créer une 1ere vue pour filtrer la partie validation :

Code:

1
2
3
4
5
6
CREATE VIEW  VALIDATION  (IdProd, Note)	
AS  
    SELECT IdProd, Note
    FROM   AVIS 
    WHERE Validation = 'oui'
;

Et vous y vous faites référence dans la vue principale :

Code:

1
2
3
4
5
6
7
8
9
10
11
CREATE VIEW VUE (id_prod, nom_prod, img_prod, id_marq, nom_marq, id_typ, nom_typ, moyenne_note)
AS
SELECT x.IdProd, x.NomProd, x.Imgprod, y.IdMarq, NomMarq, u.IdTyp, u.NomTyp, AVG(Note) AS Moyenne  
FROM   PRODUIT AS x LEFT JOIN APPARTIENT AS y ON x.IdMarq = y.IdMarq
                    LEFT JOIN VALIDATION AS z ON x.IdProd = z.IdProd
                    LEFT JOIN MARQUE AS t ON y.IdMarq = t.IdMarq 
                    LEFT JOIN AVOIR AS f ON f.idprod= x.idprod
                    LEFT JOIN TYP AS u ON f.Idtyp = u.Idtyp
WHERE  y.IdCat = 'cat01'
GROUP BY x.IdProd, x.NomProd, x.Imgprod, y.IdMarq, t.NomMarq, u.IdTyp, u.NomTyp
;

Qu'est-ce que ça donne ?

10/04/2014, 06h07
jamdinhe

Salut, c'est donc la solution au problème.

Je vous remercie encore de m'avoir aidé et conseillé, c'est très aimable de poster dans ce forum.

J'espère qu'il y aura d'autres développeurs qui vont profiter de cette discussion pour avancer dans leurs projets. :ccool:

amine

Il y a quand même quelque chose qui m'échappe, c'est la nécessité de la sous requête.

En d'autres termes, pourquoi la première requête de Exia93 ne fonctionne pas, et surtout pourquoi la sous-requête (deuxième requête de Exia93) résout le problème.

Code:

1
2
3
4
5
6
7
8
9
10
CREATE VIEW VUE (id_prod, nom_prod, img_prod, id_marq, nom_marq, id_typ, nom_typ, moyenne_note) 
AS
SELECT x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ, AVG(Note) AS Moyenne  
FROM   PRODUIT AS x LEFT JOIN APPARTIENT AS y ON x.IdMarq = y.IdMarq
                    LEFT JOIN AVIS AS z ON x.Id_Prod = z.Id_Prod AND z.validation = 'oui' -- à remplacer par la nouvelle valeur
                    LEFT JOIN MARQUE AS t ON y.Id_Marq = t.Id_Marq 
                    LEFT JOIN AVOIR AS f ON f.id_prod=x.id_prod
                    LEFT JOIN TYP AS u ON y.Id_typ = u.Id_typ
WHERE  y.IdCat = 'cat01'
GROUP BY x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ

<== cette requête devrait fonctionner aussi bien que celle là :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE VIEW VUE (id_prod, nom_prod, img_prod, id_marq, nom_marq, id_typ, nom_typ, moyenne_note) 
AS
SELECT x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ, AVG(Note) AS Moyenne  
FROM   PRODUIT AS x LEFT JOIN APPARTIENT AS y ON x.IdMarq = y.IdMarq
                    LEFT JOIN (
                            SELECT av.id_prod, av.id_int, av.note
                            FROM AVIS av
                            WHERE av.validation = 'oui'
 
                    ) AS z ON x.Id_Prod = z.Id_Prod
                    LEFT JOIN MARQUE AS t ON y.Id_Marq = t.Id_Marq 
                    LEFT JOIN AVOIR AS f ON f.id_prod=x.id_prod
                    LEFT JOIN TYP AS u ON y.Id_typ = u.Id_typ
WHERE  y.IdCat = 'cat01'
GROUP BY x.Id_Prod, x.Nom_Prod, x.Img_prod, y.Id_Marq, Nom_Marq, u.Id_Typ, u.Nom_Typ

Est-ce bien la requête avec le AND z.validation = 'oui' dans la condition de jointure externe qui avait été testée ?

10/04/2014, 18h36
fsmrel

Bonsoir aieeeuuuuu,

J’ai créé les tables et les vues avec MySQL : celui-ci rouspète effectivement quand on cherche à créer la vue définie par Exia93, avec le SELECT emboîté. Votre vue est agréée par MySQL, ça roule, et c’est la moindre des choses ! Vous avez fait simple, mais pas plus simple ^^. Dans tous les cas, dans le dernier JOIN, il faut juste remplacer y.Id_Typ par f.Id_Tp.
11/04/2014, 10h47
aieeeuuuuu

Citation:

Envoyé par fsmrel

Votre vue est agréée par MySQL, ça roule, et c’est la moindre des choses !

Ce n'est pas ma vue, c'est la requête qu'avait proposé Exia93 avant de proposer celle avec la sous requête. Mais jamdinhe avait répondu :

Citation:

malheureusement la requête n'affiche toujours que les produits ayant des notes

Et ça je ne comprend pas ! (je ne comprends pas non plus en quoi la sous requête résout ce problème...)
Je soupçonne en fait que la clause vérifiant la validation(AND z.validation = 'oui') ait été ajoutée dans le WHERE, annulant ainsi l'effet de la jointure externe... (Exia93 avait dans le même post du 09/04/2014, 10h10 mis la requête sans la vérification de la validation des avis...)
Jamdinhe, qu'en est-il ?

Pour en revenir au GROUP BY

Citation:

Envoyé par fsmrel

Bonsoir,
Il est vrai que selon la norme SQL:1999, chaque nom de colonne figurant dans la clause GROUP BY doit aussi figurer dans la clause SELECT (à moins de figurer en tant qu’argument dans une fonction d’agrégation). En l’occurrence, je fais référence à l’ouvrage de Peter Gulutzan et Truly Pelzer, SQL-99 Complete, Really, au paragraphe « Rules for Grouping Columns » aux pages 634, 635, cité aussi ici (« the Single-Value Rule » :

You must follow “The Single-Value Rule” — every Column named in the select list must also be a grouping Column, unless it is an argument for one of the set functions.

Je note en passant que les fonctions auxquelles il est fait allusion peuvent retourner des sacs plutôt de des ensembles, mais bon. Quoi qu’il en soit, quelqu’un sait-il où on en est avec la version SQL:2011 ?

En fait, il semble que la version de 1999 incluait déjà cette subtilité.
Il en est fait allusion dans cet article.

Ci on se réfère à ce document, la partie 7.9 <group by clause> est relativement muette sur le sujet. En revanche, si on va plus loin (si toutefois il nous reste de l'aspirine :) ), la partie 7.11 <query specification>, la "syntax rule" numéro 13 nous indique :

Citation:

If T is a grouped table, then let G be the set consisting of every column referenced by a <column reference> contained in the <group by clause> immediately contained in <table expression>. In each <value expression>, each <column reference> that references a column of T shall reference some column C that is functionally dependent on G or shall be contained in a <set function specification>.
11/04/2014, 16h30
fsmrel

Bonsoir aieeeuuuuu

Citation:

Envoyé par aieeeuuuuu

Ce n'est pas ma vue

Rendons à César... De fait, je n’avais pas vu la réponse du collègue (post #6) d’où comme un quiproquo, et je vous prie de m’excuser.

Citation:

Envoyé par aieeeuuuuu

En fait, il semble que la version de 1999 incluait déjà cette subtilité.
Il en est fait allusion dans cet article.

Merci pour l’article, l’auteur a eu bien du courage...