Bonjour à tous ! j'ai besoin d'un coup de main pour executer une requete qui dépasse mes capacités intellectuelles

Imaginez d'abord plusieurs "chantiers", pour lesquels, chaque ouvriers peut effectuer une commande de produits à son patron (même patron pour tous les chantiers)
La patron vient, dépose les produits qu'il faut, mais pas forcément le bon nombre.

Exemple :
Chantier 1, ouvrier commande :
5 produits "A",
3 produits "B"
10 produits "C" et
2 produits "D";

Le patron, lui, ramène sur le chantier :
4 produits "A",
4 produits "B"
5 produits "C" et
aucun produit "D"

Mon dilemmes : afficher sur une page les produits que le patron n'a pas livrés

Dans mon exemple, je veux voir afficher :

1 produit(s) A (id 1) manquant(s)
5 produit(s) C (id 3) manquant(s)
2 produit(s) D (id 4) manquants

mes tables ! (simplifiés, et avec les accents pour bien comprendre)

produits

id	nom
1	A
2	B
3	C
4	D
...	...

commandes

id	id_chantier
1	5
2	2
3	2
4	5
...	...

produits_commandés

id	id_commande	id_produit	quantité_demandée
...	...	...	...
1	4	1	5
2	4	2	3
3	4	3	10
4	4	4	2
...	...	...	...

produits_déposés

id	id_commande	id_produit	quantité_deposée
...	...	...	...
1	4	1	4
2	4	2	4
3	4	3	5
...	...	...	...

Dans ces tables, j'ai respecté mon exemple niveau nom des produits, et quantité commandées/déposées.

Sur ma page PHP, je n'ai qu'une seule variable : $numero_chantier

Je voudrais donc
AFFICHER id_du_produit, nom_du_produit, quantité_manquante
ISSUS DE LA TABLE produits
ET DE LA TABLE produits_commandés
ET DE LA TABLE produit_deposés
ET DE LA TABLE commandes
LA OU quantité_commandée - quantité_déposée > 0
DE LA DERNIERE COMMANDE EN COURS DU chantier '$numero_chantier'
TRIE PAR nom_du_produit CROISSANT

Biensur, si le patron ramène aucun produit "D", il n'y aucune ligne correspondante dans la table produits_déposés

Traduire ça en SQL, c'est mission impossible pour moi !

Au se cours !

Je serais toi je lirais la dernière commande à part et les produits déposés séparément et réglerait la différence au niveau de php avec des tableaux (ou autres structures de données).
Une autre solution, ce serait d'avoir directement dans la même table la quantité commandée et la quantité déposée... mais j'imagine que tu veux pouvoir garder une date de chaque dépôt de produits par le patron.

Citation : Corail

Une autre solution, ce serait d'avoir directement dans la même table la quantité commandée et la quantité déposée...

J'y est pensé aussi... mais ya pas forcément de dépose pour une commande, et inversement... ça complique un peu la démarche

Citation : Corail

Je serais toi je lirais la dernière commande à part et les produits déposés séparément et réglerait la différence au niveau de php avec des tableaux (ou autres structures de données)

Je voulais justement éviter

Je te guiderais bien vers les jointures externes :
http://www.sql-tutorial.net/SQL-JOIN.asp

houla ! j'ai déjà du mal avec les Left join, alors les out join ... pfiou ! ça va être chaud !

Il y a une erreur dans la conception qui complique inutilement les choses :

Dans les tables produits_commandes et produits_deposes, la clé primaire doit être (id_commande, id_produit) autrement dit un produit apparaît une seule fois par commande (avec sa quantité). Un id autogénéré ne sert à rien à part compliquer les choses, ou sous-entendre que le même produit peut apparaître plusieurs fois dans la même commande.

Que proposes-tu alors ?

en fait ça ne doit pas être si compliqué j'essaye!

SELECT id_du_produit, nom_du_produit, 
  (pc.quantité_commandée - IFNULL(pd.quantité_déposée, 0) AS quantité_manquante
FROM produits AS p
INNER JOIN produits_commandés AS pc ON p.id = pc.id_produit
INNER JOIN commandes AS c ON c.id = pc.id_commande
LEFT JOIN produit_deposés AS pd ON pc.id_commande = pd.id_commande
WHERE quantité_manquante > 0
  AND c.id = (
   SELECT max(id) FROM commande WHERE chantier = '$numero_chantier')
ORDER BY nom_du_produit ASC

LEFT JOIN = affiche null si aucune ligne dans "produit_déposés" ne correspond à la condition de jointure. Ainsi, pd.quantité_déposée = null et j'ajoute la fonction IFNULL pour gérer cela.

c.id = (DERNIERE COMMANDE EN COURS DU chantier '$numero_chantier') car j'ai supposé que la dernière commande est celle avec l'id le plus élevé = MAX (en l'absence d'une colonne date)

Dans tes 2 dernières tables les ID auto incrémenté sont pas forcément utiles pour la requête. Mais ça peut te servir quand même, laisse les ça mange pas de pain. Genre, afficher la commande n° id, la livraison n° id (aussi tu aurais pu leur mettre un nom différent)

> Que proposes-tu alors ?

Tu n'as pas lu mon post ?

> les ID auto incrémenté sont pas forcément utiles pour la requête.

Effectivement ils ne servent à rien.

> Mais ça peut te servir quand même, laisse les ça mange pas de pain.

Ca fait 1 index de plus, une organisation de la table pas optimale (sous innodb), et des opportunités de bugs XDDD

De plus ça sous-entend que le couple (commande_id, produit_id) n'est pas unique (car si il l'était, on l'aurait utilisé comme clé primaire naturelle). Si il n'est pas unique, ta requête ne marchera pas... Alors, y a-t-il une contrainte UNIQUE dessus ?

t'a raison. dans "produits_commandés" on peut supposer que (commande_id,produit_id) est unique - et encore à voir?
mais dans "produits_déposés" puisqu'il en manque, il y aura forcément une 2eme livraison donc le couple ne sera plus unique.

Cyril si tu nous confirme ça, ok ma requête ne marchera pas et ça justifie les id sur ces 2 tables. mais je corrigerais ma requête

Mais si le mec fait plusieurs livraisons pour une commande, alors il faudrait une table livraisons, avec la date entre autres... du coup la clé primaire de produits_deposes est (livraison_id, produit_id).

Problème typique du site de vente en ligne : une commande peut être livrée en plusieurs colis, chacun contenant plusieurs produits, donc tu as 2 relations et une table pour les colis...

donc y'a un petit trou à boucher dans l'énoncé !

Citation : pifou25

en fait ça ne doit pas être si compliqué j'essaye!

SELECT id_du_produit, nom_du_produit, 
  (pc.quantité_commandée - IFNULL(pd.quantité_déposée, 0) AS quantité_manquante
FROM produits AS p
INNER JOIN produits_commandés AS pc ON p.id = pc.id_produit
INNER JOIN commandes AS c ON c.id = pc.id_commande
LEFT JOIN produit_deposés AS pd ON pc.id_commande = pd.id_commande
WHERE quantité_manquante > 0
  AND c.id = (
   SELECT max(id) FROM commande WHERE chantier = '$numero_chantier')
ORDER BY nom_du_produit ASC

LEFT JOIN = affiche null si aucune ligne dans "produit_déposés" ne correspond à la condition de jointure. Ainsi, pd.quantité_déposée = null et j'ajoute la fonction IFNULL pour gérer cela.

c.id = (DERNIERE COMMANDE EN COURS DU chantier '$numero_chantier') car j'ai supposé que la dernière commande est celle avec l'id le plus élevé = MAX (en l'absence d'une colonne date)

Dans tes 2 dernières tables les ID auto incrémenté sont pas forcément utiles pour la requête. Mais ça peut te servir quand même, laisse les ça mange pas de pain. Genre, afficher la commande n° id, la livraison n° id (aussi tu aurais pu leur mettre un nom différent)

ça à l'air tellement juste, mais ça me retourne rien

Je vais encore cherche pourquoi !

Je le tiens presque !

EDIT !

C'est bon ! je le tiens !
En, fait, il se mélangait les pinceaux avec d'autres anciennes commandes..., j'ai donc rajouter une condition dans la clause ON du LEFT JOIN

Voici la requete finale :

SELECT id_du_produit, nom_du_produit, (pc.quantité_commandée - IFNULL(pd.quantité_déposée, 0) AS quantité_manquante
FROM produits AS p
INNER JOIN produits_commandés AS pc 
ON (p.id = pc.id_produit)
LEFT JOIN produit_deposés AS pd 
ON (p.id = pd.id_produit AND pd.id_commande=(SELECT max(id) FROM commande WHERE chantier = '$numero_chantier'))
INNER JOIN commandes AS c 
ON (c.id = pc.id_commande)
WHERE (pc.quantité_commandée - IFNULL(pd.quantité_déposée, 0) > 0
AND c.id = (SELECT max(id) FROM commande WHERE chantier = '$numero_chantier')
ORDER BY nom_du_produit ASC

Merci beaucoup !