Discussion :

[gabkiel]

Bonjour,
il s'agit d'un base de données Access mettant en relation des recettes de cuisines,leurs ingrédients respectifs ainsi que les ingrédients
contenu dans un stock.

Tables:

RECETTE
id_recette
nom_recette
categorie
type

RECING
#id_recette(clé primaire étrangère)
#id_ingredient(clé primaire étrangère)
quantite_ingredient
mesure_ingredient

INGREDIENT
id_ingredient
nom
quantite
mesure

Pour sélectionner toutes les informations de la table recette->

Code :

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

SELECT * FROM RECETTE WHERE categorie LIKE'%Entrée%';

Maintenant je voudrais filtrer encore plus les réponses en s'intéressant à la quantité des ingrédients contenu dans le frigo(quantite)
et celle contenu dans une recette(quantite_ingredient).Je voudrais complèter la requete précédente avec
"AND quantite>quantite_ingredient".

Le problème est que chaque recette possède plusieurs ingrédients et certains répondent à "quantite>quantite_ingredient"
et d'autres non;
un exemple:

INGREDIENT quantite quantite_ingredient id_recette
oeuf 2 1 1
farine 100 200 1

la quantité d'œuf est validé mais pas celle de farine, la recette 1 va s'afficher car au moins un des ingrédient à les bonnes quantités.
Je voudrais avoir comme réponse à ma requête seulement les recettes ou tous les ingrédients possèdent les bonnes quantités.

Mon projet est développé en vb.net et est relié à une base Access.

J'attends avec impatience des commentaires et surtout si possible une ébauche de solution

merci d'avance
gab

[Snipah]

Bonjour,

Vous pouvez essayer de comparer pour chaque recette le nombre d'ingredient necessaire avec le nombre d'ingredient disponibles en quantité suffisante :

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
 
with liste as
(
select 
      r.id_recette,
      r.nom_recette, 
      i.id_ingredient, 
      i.nom_ingredient, 
      ri.quantite_ingredient as quantite_necessaire, 
      i.quantite as quantite_dispo 
from recette r
join recing ri on r.id_recette = ri.id_recette
left join ingredient i on ri.id_ingredient = i.id_ingredient 
)
select ld.id_recette
from liste ld
where ld.quantite_necessaire <= ld.quantite_dispo
group by ld.id_recette
having count(ld.id_ingredient) = (select count(ln.id_ingredient) from liste ln where ln.id_recette = ld.id_recette)

Je ne crois pas qu'Access permette d'utiliser le WITH, donc remplacez "liste" dans le select par la requête adéquate.

[gabkiel]

Bonjour et merci Snipah,

Cependant je connais pas l'utilisation du 'WITH' et donc je suis incapable de trouver son équivalent pour access. Je vois bien le principe (cela me fait penser aux requetes imbriquées).
Je ne connais que le 'In (SELECT ..)' pour effectuer une telle chose ceci donnerais :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT R.id_recette,
RE.quantite_ingredient AS quantite_necessaire,
I.quantite AS quantite_dispo 
FROM RECETTE R,RECING RE,INGREDIENT I
WHERE R.id_recette = RE.id_recette
AND RE.id_ingredient=I.id_ingredient
AND ld.quantite_necessaire <= ld.quantite_dispo
GROUP BY RE.id_recette
HAVING count(RE.id_ingredient) IN (SELECT count(RE.id_ingredient) FROM RECETTE R,RECING RE WHERE R.id_recette = RE.id_recette)

[Snipah]

Essayez celle-ci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT r.id_recette, r.nom_recette
FROM recette r
INNER JOIN recing ri ON r.id_recette = ri.id_recette
LEFT OUTER JOIN ingredient i ON ri.id_ingredient = i.id_ingredient
WHERE ri.quantite_necessaire <= i.quantite_dispo
GROUP BY r.id_recette, r.nom_recette
HAVING count(*) = (
                   SELECT count(*) 
                   FROM recette r2
                   INNER JOIN recing ri2 ON r.id_recette = ri.id_recette
                   WHERE r2.id_recette = r.id_recette
                  )

Cette solution semble suffire dans votre cas, mais vous pouvez quand même lire l'article d'SQLPro sur la division relationnelle.

[gabkiel]

Bonjour Snipah,

J'ai essayé la requête sous access

code:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT r.id_recette, r.nom_recette
FROM recette r
INNER JOIN recing ri ON r.id_recette = ri.id_recette
LEFT OUTER JOIN ingredient i ON ri.id_ingredient = i.id_ingredient
WHERE ri.quantite_ingredient <= i.quantite
GROUP BY r.id_recette, r.nom_recette
HAVING count(*) = (
                   SELECT count(*) 
                   FROM recette r2
                   INNER JOIN recing ri2 ON r.id_recette = ri.id_recette
                   WHERE r2.id_recette = r.id_recette
                 )

Cependant lors de son exécution Access trouve une erreur de syntaxe(opérateur absent) sur "LEFT OUTER JOIN ingredient i ON ri.id_ingredient = i.id_ingredient".
Ayant jeté un œil à l'article d'SQLPro sur la division relationnelle ,la syntaxe me parait correct...du moins pour cette ligne

[Tofalu]

Access ne gère par les jointures Outer Join

[CinePhil]

Access ne gère par les jointures Outer Join

J'espère qu'il accepte LEFT JOIN sans OUTER quand même !

[Tofalu]

J'espère qu'il accepte LEFT JOIN sans OUTER quand même !

Oui, oui

[gabkiel]

Bonjour,

alors j'ai enlevé le OUTER ce qui me donne ceci:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT r.id_recette, r.nom_recette
FROM recette r
INNER JOIN recing ri ON r.id_recette = ri.id_recette
LEFT JOIN ingredient i ON ri.id_ingredient = i.id_ingredient
WHERE ri.quantite_ingredient <= i.quantite
GROUP BY r.id_recette, r.nom_recette
HAVING count(*) = (
                   SELECT count(*) 
                   FROM recette r2
                   INNER JOIN recing ri2 ON r.id_recette = ri.id_recette
                   WHERE r2.id_recette = r.id_recette
                 )

J'ai encore l'erreur sur :

Code :

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

1
2
 
LEFT JOIN ingredient i ON ri.id_ingredient = i.id_ingredient

Est ce que je doit remettre en question la construction de ma base ?
Est t-il normal que j'ai des champs doublons à cause de mes clés primaires et de mes clés primaires étrangères?

table recette:clé primaire 'id_recette'
table recing:clé primaire & étrangère 'id_recette' & 'id_ingrédient'
table ingrédient:clé primaire 'id_ingrédient'


merci

gab

[CinePhil]

Si j'ai bien compris votre problème de départ, vous cherchez les recettes qui ne demandent pas de quantités d'ingrédients supérieures à celles qui existent en stock ?

1) Voici la requête qui va donner le nombre d'ingrédients en quantité suffisante par recette (on suppose que les unités de mesure des recettes sont les mêmes que celles des ingrédients) :

Code :

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

1
2
3
4
5
6
SELECT r1.id_recette, r1.nom_recette, COUNT(*) AS NbIngredientsOK
FROM RECETTE AS r1
INNER JOIN RECING AS ri1 ON r1.id_recette = ri1.id_recette
  INNER JOIN INGREDIENT AS i1 ON ri1.id_ingredient = i1.id_ingredient
WHERE ri1.quantite_ingredient <= i1.quantite_ingredient
GROUP BY r1.id_recette

2) Voici la requête qui va donner le nombre d'ingrédients par recette :

Code :

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

1
2
3
4
SELECT r2.id_recette, r2.nom_recette, COUNT(*) AS NbIngredients
FROM RECETTE AS r2
INNER JOIN RECING AS ri2 ON r2.id_recette = ri2.id_recette
GROUP BY r2.id_recette

Il suffit maintenant de comparer les deux pour obtenir la liste des recettes faisables :

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 t1.nom_recette
FROM (
  SELECT r1.id_recette, r1.nom_recette, COUNT(*) AS NbIngredientsOK
  FROM RECETTE AS r1
  INNER JOIN RECING AS ri1 ON r1.id_recette = ri1.id_recette
    INNER JOIN INGREDIENT AS i1 ON ri1.id_ingredient = i1.id_ingredient
  WHERE ri1.quantite_ingredient <= i1.quantite_ingredient
  GROUP BY r1.id_recette
) AS t1
INNER JOIN (
  SELECT r2.id_recette, r2.nom_recette, COUNT(*) AS NbIngredients
  FROM RECETTE AS r2
  INNER JOIN RECING AS ri2 ON r2.id_recette = ri2.id_recette
  GROUP BY r2.id_recette
) AS t2 ON t1.id_recette = t2.id_recette
WHERE t1.NbIngredientsOK = t2.NbIngredients
ORDER BY t1.nom_recette

C'est du SQL Standard, je ne sais pas comment Access va avaler ça !
Peut-être qu'il n'aime pas les sous requêtes et qu'il faudra en passer par la création individuelle des deux sous requêtes et la création de la troisième à partir des deux autres.

Au fait, puisque les ingrédients des recettes existent forcément dans la table des ingrédients, un INNER JOIN suffit. Pas besoin d'un LEFT OUTER JOIN.