Requête en partant du résultas de sous requêtes [Résolu]

[Renand]

Bonjour à tous, voilà je commence à craquer sur une requête.

Je dispose de 3 tables.
1 table avec des produits (products)
1 table avec des tissus (tissues)
1 table de relation avec quelques données complémentaires sur la relation (productrepartisions)

(je mets ici les champs qui m'occupent)
+-------------+
| products |
+--------------+
| id |
| consumption |
+---------------+

+---------+
| tissues |
+---------+
| id |
| stock |
+---------+

+---------------------+
| productrepartisions |
+---------------------+
| id |
| productid |
| tissueid |
| quantity |
| quantityweb |
+---------------------+

Je dois trouver le moyen de calculer la consommation faite pour la déduire de tissues.stock en partant de l'id d'un tissu.

je suis parti dans cette direction :

Code :

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
SELECT (
    SELECT (t.stock -
        ( SELECT
            ( SELECT COUNT(pp.modelsizeid)
                FROM productparts pp
                    INNER JOIN productrepartitions pr
                        ON pr.productid = pp.productid
                WHERE pr.tissueid = 1
                    AND pp.productid = pro.id
            ) AS grada ) 
            *
            ( SELECT
                (    SELECT (SUM(pr.quantity + pr.quantityweb) * p.consumption)
                    FROM productrepartitions pr
                        INNER JOIN products p
                            ON p.id = pr.productid
                    WHERE pr.tissueid = 1
                        AND pr.productid = pro.id  
            ) AS qte )
        ) 
        FROM tissues t
            INNER JOIN productrepartitions pr
                    ON pr.tissueid = t.id
            INNER JOIN products pro
                    ON pro.id = pr.productid    
        WHERE t.id = 1
        GROUP BY t.id
) AS myConso

Le problème est que cette requête ne retourne que le stock - la consommation du premier produit associé, j'en ai X avec chacun sa propre valeur de consommation (p.consumption).

Il est vrai que je ne suis pas un killer niveau SQL, mais pour le coup je suis perdu.
Mon raisonnement étant de partir de calculs effectués dans des sous-requêtes pour disposer des valeurs nécessaires.

Toute votre aide sera grandement appréciée.

D'avance merci

[CinePhil]

Je dois trouver le moyen de calculer la consommation faite pour la déduire de tissues.stock en partant de l'id d'un tissu.

Quelle est la consommation de chaque produit pour le tissu d'identifiant 1 ?

Code :

1
2
3
4
5
6
7
SELECT p.id, 
    SUM(p.consumption * pr.quantity) AS quantite_consommee,
    SUM(p.consumption * pr.quantityweb) AS quantite_consommee_web
FROM products p
INNER JOIN productrepartisions pr ON pr.productid = p.id
WHERE pr.tissueid = 1
GROUP BY p.id

Par contre, je vois dans ta requête d'autres tables dont tu ne parles pas dans la structure que tu nous donnes alors peut-être que ma requête ne répond que partiellement à ta demande mais ça doit te donner une piste.

[Renand]

Envoyé par CinePhil

Quelle est la consommation de chaque produit pour

Code :

Par contre, je vois dans ta requête d'autres tables dont tu ne parles pas dans la structure que tu nous donnes alors peut-être que ma requête ne répond que partiellement à ta demande mais ça doit te donner une piste.

[/QUOTE]

Outch/// la piste semble bonne !
En fait oui, j'ai oublié de parler d'une table, et d'un détail.
La table en question me permet de connaitre le nombre de tailles prévues pour le vêtement, ce qui sera le multiplicateur du résultat de la requête présente au-dessus.

Et le détail est qu'au final, il me faut une valeur numérique, en fait cette requête est une propriété calculée d'un objet. En gros, quand j'instancie le tissu, je doit pourvoir retourner la valeur pas un simple tissues.consumption.
Mon framework le gère, mais il faut qu'il n'y ait qu'une seule ligne en retour.

Pour le nombre de tailles, j'ai effectivement une table nommée productparts

+----------------+
| productsparts |
| id |
| productid |
| partid |
| modelsizeid |
+----------------+

Je compte faire un COUNT dessus pour trouver le multiple qui convient.

[CinePhil]

Et le détail est qu'au final, il me faut une valeur numérique

Euh... là je ne comprends plus ! Il te faut une seule valeur, donc la consommation d'un seul produit pour un tissu, ou bien la consommation de tous les produits utilisés par le tissu ?

Tu peux expliquer mieux la structure de la BDD et le besoin ?

[Renand]

En gros,

en partant sur cette variante

Code :

1
2
3
4
5
6
7
 
SELECT SUM(p.consumption * pr.quantity) + SUM(p.consumption * pr.quantityweb) AS conso 
FROM products p
	INNER JOIN productrepartitions pr 
		ON pr.productid = p.id
WHERE pr.tissueid = 1
GROUP BY p.id

en en multipliant le résultat de SELECT SUM(p.consumption * pr.quantity) + SUM(p.consumption * pr.quantityweb) AS conso par le résultat de la requête permettant de connaitre le nombre de tailles pour un produit.

Il me faudra retourner le total.

Je ne sais pas trop comment intégrer proprement la sous-requête dans la première, en fait.

[Renand]

Envoyé par CinePhil

Euh... là je ne comprends plus ! Il te faut une seule valeur, donc la consommation d'un seul produit pour un tissu, ou bien la consommation de tous les produits utilisés par le tissu ?

Tu peux expliquer mieux la structure de la BDD et le besoin ?

Oui il me faut la consommation totale de tous les produits pour un tissu en une valeur.

[vmolines]

Envoyé par Renand

Oui il me faut la consommation total de tous les produits pour un tissu en une valeur

Ca c'est le besoin mais il faut aussi la structure.

[CinePhil]

Bizarre comme besoin !
Si ton tissu consomme du fil (25 000 mètres), de la teinture (2 litres), tu vas additionner les mètres et les litres ?

Lis la phrase en bleu de ma signature et applique son principe parce que j'ai toujours du mal à comprendre !

[Renand]

Pardon, reprenons..

pour la structure, ça se base donc sur quatre tables.

Code :

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
 
+-------------+ 
| products | 
+--------------+ 
| id |
| consumption | 
+---------------+
 
+---------+ 
| tissues  | 
+---------+ 
| id         |
| stock    | 
+---------+
 
+----------------------+ 
| productrepartisions | 
+----------------------+ 
| id                        |
| productid              |
| tissueid                |
| quantity                |
| quantityweb          | 
+---------------------+
 
+----------------+
| productsparts |
+----------------+
| id                 |
| productid       |
| modelsizeid    |
+----------------+

PS: il n'y a ici que les champs concernés par les données à croiser, il en existe quelques autres.. mais ça ne concerne pas le besoin.

Les consommations dont je parle sont des mètres de tissus.
Chaque produit est paramétré avec une valeur de consommation moyenne (products.consumption) ex : 0.16 ou 1.50...

Je cherche à projeter sur le stock initial (tissues.stock) la consommation dans un tissu de tous les produits utilisant ce tissu.

Pour ce faire, j'ai donc une table products où sont référencés les produits, la conso moyenne pour ce produit.

Puis une liste de tissus avec pour chacun un stock initial (des mètres linéaires).

Ensuite, j'ai deux tables d'associations. L'une permettant d'associer les produits avec les tailles à confectionner. Il s'agit de productparts. Pour chaque taille de produit, j'ai une entrée.

Enfin, une autre table d'association (productrepartitions) qui permet de saisir, pour une association tissu / produit, les quantités à fabriquer (il s'agit de deux valeurs pour une question d'organisation, mais on considère le total des deux, c'est juste séparé pour les besoins de l'application).

Ce que je veux faire :

Pour le tissu 1, calculer le nombre total de produits à fabriquer (productrepartitions.quatity + productrepartitions.quantityweb), le multiplier par le nombre de tailles à faire (Count(productparts.modelsizeid) et multiplier cela par la consommation moyenne du produit products.consumption.

Le résultat devant être une valeur numérique (pas un recordset avec plusieurs résultats).



et Merci pour votre aide....

[CinePhil]

Avec cette phrase, ça devient déjà plus clair :

Je cherche à projeter sur le stock initial (tissues.stock) la consommation dans un tissu de tous les produits utilisants ce tissu.

Ce n'est pas le tissu qui utilise les produits mais les produits qui utilisent le tissu !

Si j'ai tout compris, essaie cette requête :

Code :

1
2
3
4
5
6
SELECT t.stock - SUM(((pr.quantity + pr.quantityweb)) * COUNT(pp.modelsized) * p.consumption) AS stock_restant
FROM products p
INNER JOIN productsparts pp ON pp.productid = p.id
INNER JOIN productrepartisions pr ON pr.productid = p.id
    INNER JOIN tissues t ON t.id = pr.tissueid
WHERE t.id = 1

[Renand]

Code :

1
2
3
4
5
6
SELECT t.stock - SUM(((pr.quantity + pr.quantityweb)) * COUNT(pp.modelsizeid) * p.consumption) AS stock_restant
FROM products p
INNER JOIN productparts pp ON pp.productid = p.id
INNER JOIN productrepartitions pr ON pr.productid = p.id
    INNER JOIN tissues t ON t.id = pr.tissueid
WHERE t.id = 1

me retourne Invalid use of group function

[Renand]

j'obtiens un résultat valide en supprimant

* COUNT(pp.modelsizeid)

Soit

Code :

1
2
3
4
5
6
7
 
SELECT t.stock - SUM(((pr.quantity + pr.quantityweb)) * p.consumption) AS stock_restant
FROM products p
INNER JOIN productparts pp ON pp.productid = p.id
INNER JOIN productrepartitions pr ON pr.productid = p.id
INNER JOIN tissues t ON t.id = pr.tissueid
WHERE t.id = 1

J'imagine que la jointure sur productparts permet de faire le lien...

MERCI !!!!! vraiment merci.

[CinePhil]

Oui effectivement, le comptage ne semble pas nécessaire.

[Renand]

pour vraiment laisser une trace propre, j'ai ajouté un modification afin de tenir compte des valeurs NULL dans les quantités

Code :

1
2
3
4
5
6
7
SELECT t.stock - SUM(((COALESCE(pr.quantity,0) + COALESCE(pr.quantityweb,0))) * p.consumption) AS stock_restant
FROM products p
INNER JOIN productparts pp  ON pp.productid = p.id
INNER JOIN productrepartitions pr ON pr.productid = p.id
INNER JOIN tissues t ON t.id = pr.tissueid
WHERE t.id = tissues.id