Discussion :

[totoAussi]

Bonjour,

je cherche la bonne requête mysql qui permette de trouver les noms des catégories à condition que le nombre de marques différentes dans chacune de ces catégories soit supérieur ou égal à 3 et que le nombre de produits dans chacun de ces marques soit supérieur ou égal à 2.

D'abord je vous présente les 3 tables :

1) La table category (catégorie) qui a 2 colonnes :
- id_category : auto-increment
- name : varchar

Et voici les données :

Code :

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

1
2
3
4
5
id_category | name
----------------
1 | pull
2 | pantalon
3 | chaussure

2) La table manufacturer (marque) qui a 2 colonnes :
- id_manufacturer : auto-increment
- name : varchar

Et voici les données :

Code :

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

1
2
3
4
5
id_manufacturer | name
------------------------------------
1 | adidas
2 | nike
3 | puma

3) et la table product (produit) qui a 4 colonnes :
- id_product : auto-increment
- id_category : int
- id_manufacturer : int
- name : varchar

Et voici les données :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
id_product | id_category | id_manufacturer | name
---------------------------------------------------------
1 | 1 | 1 | 1 | adidas pull rouge
2 | 1 | 1 | 1 | adidas pull vert
3 | 2 | 2 | 1 | adidas pantalon rouge
4 | 2 | 2 | 1 | adidas pantalon vert
5 | 2 | 2 | 2 | nike pantalon rouge
6 | 2 | 2 | 2 | nike pantalon vert
7 | 2 | 2 | 3 | puma pantalon rouge
8 | 2 | 2 | 3 | puma pantalon verte 
9 | 3 | 3 | 1 | adidas chaussure rouge

Donc la requête que je cherche doit m'afficher comme résultat la catégorie "pantalon", car d'après la table product, cette catégorie a 3 marques différentes (adidas, nike, puma) et que dans chacune de ces marques, il y a au moins 2 produits (pantalon rouge et pantalon vert pour adidas, pantalon rouge et pantalon vert pour nike, pantalon rouge et pantalon vert pour puma).

Avez-vous une idée ?

Merci d'avance pour vos réponses, cordialement

[Maljuna Kris]

Saluton,
Tu veux l'id_product, l'id_manufacturer, et l'id_category

Code mysql :

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

1
2
3
4
5
SELECT id_product, c.id-category, m.id_manufacturer
FROM product
INNER JOIN category c USING (id_product)
INNER JOIN manufacturer m USING (id_manufacturer)
GROUP BY id_product, c.id-category, m.id_manufacturer

Ensuite il te suffit de filtrer avec un clause HAVING

Code mysql :

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

1
2
HAVING COUNT(DISTINCT m.id_manufacturer) >= 2
AND COUNT(DISTINCT c.id_category)>=3

[totoAussi]

Bonjour,

merci pour ta réponse, mais ça ne marche pas.

Je sais que la requête qui permet d'avoir les noms de catégories qui ont plus de 3 marques différentes dans la table product est :

Code :

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

1
2
3
4
5
SELECT category.name 
FROM category, product
WHERE category.id_category = product.id_category 
GROUP BY id_category 
HAVING COUNT( DISTINCT id_manufacturer ) >3

Mais cette requête ne me dit pas si chacune des différentes marques des catégories trouvées a au moins 2 produits dans la table product.

Une idée ?

Merci d'avance, cordialement

[Maljuna Kris]

Alors il faut faire le comptage des id_manufacturer dans une sous-requêtes corrélée sur l'id_product et l'id_category.

[totoAussi]

Rebonjour,

merci pour ta réponse, mais j'ai cherché à faire ce que tu m'as dit pendant des heures, mais j'ai pas réussi à trouver la requête miracle.

En tout cas j'ai 2 pistes :

1) Je sais que la requête qui permet d'avoir les noms de catégories qui ont plus de 3 marques différentes dans la table product est :

Code :

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

1
2
3
4
5
SELECT category.name 
FROM category, product
WHERE category.id_category = product.id_category 
GROUP BY id_category 
HAVING COUNT( DISTINCT id_manufacturer ) >3

Mais cette requête ne me dit pas si chacune des différentes marques des catégories trouvées a au moins 2 produits dans la table product.

2) et je sais que la requête qui permet d'avoir le nombre de marques différentes (manufacturer.name) d'une catégorie (id_category = 2 par exemple) qui ont au moins 2 produits dans la table product est :

Code :

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

1
2
3
4
5
6
7
8
 
 
SELECT COUNT(*) FROM (SELECT manufacturer.name
			FROM manufacturer, product
			WHERE manufacturer.id_manufacturer = product.id_manufacturer
			AND product.id_category =  '2'
			GROUP BY product.id_manufacturer
			HAVING COUNT( DISTINCT product.id_product ) >= 2)

Mais je ne parviens toujours pas à combiner les 2 requêtes.

J'ai essayé avec ça :

Code :

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

1
2
3
4
5
6
SELECT category.name, category.id_category, COUNT( DISTINCT id_manufacturer ) AS nombre_distinct_manufacturer, COUNT( DISTINCT id_product ) AS nombre_product
FROM category, product 
WHERE category.id_category = product.id_category 
GROUP BY product.id_category, product.id_manufacturer
HAVING nombre_distinct_manufacturer >3 
AND nombre_product >2

Mais je ne sais pas si c'est correct.

Une idée ?

Merci d'avance, cordialement.

[Maljuna Kris]

Dans la table product, est-ce que le trio id_product, id_category, id_manufacturer constitue un index UNIQUE ?

[totoAussi]

Bonjour,

non, seul id_product est unique car auto-incrémenté dans la table product.
id_category et id_manufacturer peuvent être présents en plusieurs fois dans la table product.

A+

[Maljuna Kris]

Bon en raisonnant correctement, , j'arrive à cette proposition de requête

Code mysql :

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

1
2
3
4
5
6
7
8
9
10
SELECT t1.produit
FROM ( SELECT id_produt, id_category
       FROM product
       GROUP BY id_produt, id_category
       HAVING COUNT(*) => 3) t1
INNER JOIN (SELECT id.product, id_category, ID_manufacturer
            FROM product
            GROUP BY  id.product, ID_manufacturer
            HAVING COUNT(*) => 2) t2
        USING (id_produt, id_category)

Mais j'intuite qu'elle n'est pas très logique parce que le deuxième GROUP BY ne porte pas sur l'ensemble des colonnes du SELECT. D'ailleurs d'autres SGBD que MYSQL n'accepterait pas cette syntaxe.

[totoAussi]

Merci, je vais tester ça.

Je découvre maintenant que le language sql demande vraiment une logique pure.

Je te tiens courant

[totoAussi]

Au fait, excuse moi,

à quoi correspond t1 et t2 svp ?

A+

[Maljuna Kris]

Au fait, excuse moi,

à quoi correspond t1 et t2 svp ?

A+

Ce sont des alias pour les sous-requêtes, de manière à pouvoir les utiliser comme des tables temporaires dans la requête principale.

[totoAussi]

Ok, merci.

Mais je crois que j'ai presque trouvé :

Code :

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
 
 
SELECT category.name, category.id_category, 
(
	SELECT COUNT(*) FROM 
	(
			SELECT product.id_manufacturer 
			FROM product 
			GROUP BY product.id_manufacturer
			HAVING COUNT( DISTINCT product.id_product ) >= 2
	)
 
) AS nombre_distinct_manufacturer
FROM category, product
WHERE category.id_category = product.id_category 
GROUP BY product.id_category
HAVING nombre_distinct_manufacturer >= 3

Mais mysql m'affiche cette erreur :

Code :

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

1
2
 
Every derived table must have its own alias

Une idée ?

Merci d'avance, cdt.

[Maljuna Kris]

La sous-sous-requête n'est pas aliassée.
Mais je doute que le résultat ait un sens, cette sous-sous-requête n'étant pas du tout corrélée.

[CinePhil]

trouver les noms des catégories à condition que le nombre de marques différentes dans chacune de ces catégories soit supérieur ou égal à 3

On va d'abord se contenter de l'identifiant de la catégorie.

Code :

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

1
2
3
4
SELECT id_category
FROM product
GROUP BY id_category
HAVING COUNT(DISTINCT id_manufacturer) > 2

Quelles sont ces marques ?

Code :

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

1
2
3
4
5
6
7
8
9
SELECT DISTINCT p.id_manufacturer
FROM product p
INNER JOIN
(
    SELECT id_category
    FROM product
    GROUP BY id_category
    HAVING COUNT(DISTINCT id_manufacturer) > 2
) t ON t.id_category = p.id_category

Parmi ces marques, quelles sont celles qui ont au moins deux produits ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT p.id_manufacturer
FROM product p
INNER JOIN
(
    SELECT id_category
    FROM product
    GROUP BY id_category
    HAVING COUNT(DISTINCT id_manufacturer) > 2
) t ON t.id_category = p.id_category
GROUP BY p.id_manufacturer
HAVING COUNT(p.id_product) > 1

Quels sont les produits de ces marques ?

Code :

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
SELECT p2.id_product
FROM product p2
INNER JOIN 
(
    SELECT p.id_manufacturer
    FROM product p
    INNER JOIN
    (
        SELECT id_category
        FROM product
        GROUP BY id_category
        HAVING COUNT(DISTINCT id_manufacturer) > 2
    ) t1 ON t1.id_category = p.id_category
    GROUP BY p.id_manufacturer
    HAVING COUNT(p.id_product) > 1
) t2 ON t2.id_manufacturer = p2.id_manufacturer

Quelles sont les catégories de ces produits ?

Code :

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
SELECT DISTINCT c.name AS category_name
FROM category c
INNER JOIN product p3 On p3.id_category = c.id_category
    INNER JOIN
    (
        SELECT p2.id_product
        FROM product p2
        INNER JOIN 
        (
            SELECT p.id_manufacturer
            FROM product p
            INNER JOIN
            (
                SELECT id_category
                FROM product
                GROUP BY id_category
                HAVING COUNT(DISTINCT id_manufacturer) > 2
            ) t1 ON t1.id_category = p.id_category
            GROUP BY p.id_manufacturer
            HAVING COUNT(p.id_product) > 1
        ) t2 ON t2.id_manufacturer = p2.id_manufacturer
    ) t3 ON t3.id_product = p3.id_product

C'est peut-être simplifiable en prenant le problème par l'autre bout mais j'ai faim !