Requête SQL via une table intermédiaire
étant donné les tableaux suivants:
Recipes
| id | name
| 1 | 'chocolate cream pie'
| 2 | 'banana cream pie'
| 3 | 'chocolate banana surprise'
Ingredients
| id | name
| 1 | 'banana'
| 2 | 'cream'
| 3 | 'chocolate'
RecipeIngredients
| recipe_id | ingredient_id
| 1 | 2
| 1 | 3
| 2 | 1
| 2 | 2
| 3 | 1
| 3 | 3
Comment puis-je construire une requête SQL pour trouver des recettes où les ingrédients.nom = "chocolat"et ingrédients.nom = "crème"?
6 réponses
c'est ce qu'on appelle la division relationnelle. Diverses techniques sont discutées ici.
une alternative non encore donnée est le double N'existe pas
SELECT r.id, r.name
FROM Recipes r
WHERE NOT EXISTS (SELECT * FROM Ingredients i
WHERE name IN ('chocolate', 'cream')
AND NOT EXISTS
(SELECT * FROM RecipeIngredients ri
WHERE ri.recipe_id = r.id
AND ri.ingredient_id = i.id))
Utilisation:
SELECT r.name
FROM RECIPES r
JOIN RECIPEINGREDIENTS ri ON ri.recipe_id = r.id
JOIN INGREDIENTS i ON i.id = ri.ingredient_id
AND i.name IN ('chocolate', 'cream')
GROUP BY r.name
HAVING COUNT(DISTINCT i.name) = 2
le point clé ici est que le nombre doit être égal au nombre de noms d'ingrédients. Si ce n'est pas un compte distinct, il y a un risque de faux positifs à cause des doubles.
si vous cherchez des associations multiples, alors la façon la plus simple d'écrire la requête est d'utiliser multiple EXISTS
conditions au lieu d'une simple ligne droite JOIN
.
SELECT r.id, r.name
FROM Recipes r
WHERE EXISTS
(
SELECT 1
FROM RecipeIngredients ri
INNER JOIN Ingredients i
ON i.id = ri.ingredient_id
WHERE ri.recipe_id = r.id
AND i.name = 'chocolate'
)
AND EXISTS
(
SELECT 1
FROM RecipeIngredients ri
INNER JOIN Ingredients i
ON i.id = ri.ingredient_id
WHERE ri.recipe_id = r.id
AND i.name = 'cream'
)
si vous êtes certain que les associations sont uniques (c.-à-d. qu'une seule recette ne peut avoir qu'une seule instance de chaque ingrédient), alors vous pouvez tricher un peu en utilisant un sous-jeu de regroupement avec un COUNT
fonction et, éventuellement, l'accélérer (performance dépendra de la SGBD):
SELECT r.id, r.Name
FROM Recipes r
INNER JOIN RecipeIngredients ri
ON ri.recipe_id = r.id
INNER JOIN Ingredients i
ON i.id = ri.ingredient_id
WHERE i.name IN ('chocolate', 'cream')
GROUP BY r.id, r.Name
HAVING COUNT(*) = 2
Ou, si une recette peut avoir plusieurs instances d'un même ingrédient (pas de UNIQUE
contrainte sur l' RecipeIngredients
table d'association), vous pouvez remplacer la dernière ligne avec:
HAVING COUNT(DISTINCT i.name) = 2
select r.*
from Recipes r
inner join (
select ri.recipe_id
from RecipeIngredients ri
inner join Ingredients i on ri.ingredient_id = i.id
where i.name in ('chocolate', 'cream')
group by ri.recipe_id
having count(distinct ri.ingredient_id) = 2
) rm on r.id = rm.recipe_id
SELECT DISTINCT r.id, r.name
FROM Recipes r
INNER JOIN RecipeIngredients ri ON
ri.recipe_id = r.id
INNER JOIN Ingredients i ON
i.id = ri.ingredient_id
WHERE
i.name IN ( 'cream', 'chocolate' )
édité le commentaire suivant, merci! C'est le droit chemin, puis:
SELECT DISTINCT r.id, r.name
FROM Recipes r
INNER JOIN RecipeIngredients ri ON
ri.recipe_id = r.id
INNER JOIN Ingredients i ON
i.id = ri.ingredient_id AND
i.name = 'cream'
INNER JOIN Ingredients i2 ON
i2.id = ri.ingredient_id AND
i2.name = 'chocolate'
une autre façon:
Version 2 (procédure stockée) révisé
select r.name
from recipes r
where r.id = (select t1.recipe_id
from RecipeIngredients t1 inner join
RecipeIngredients t2 on t1.recipe_id = t2.recipe_id
and t1.ingredient_id = @recipeId1
and t2.ingredient_id = @recipeId2)
Modifier 2: [avant que les gens se mettent à crier] :)
cela peut être placé en haut de la version 2, ce qui permettra d'interroger par nom au lieu de passer dans l'id.
select @recipeId1 = recipe_id from Ingredients where name = @Ingredient1
select @recipeId2 = recipe_id from Ingredients where name = @Ingredient2
j'ai testé la version 2, et ça marche. La plupart des utilisateurs où le lien sur la table D'ingrédients, dans ce cas n'était pas nécessaire!
Modifier 3: (résultats des tests);
Lorsque cette procédure stockée est exécutée ce sont les résultats.
Les résultats sont au format (Première Recipe_id ; Deuxième Recipe_id, Résultat)
1,1, Failed
1,2, 'banana cream pie'
1,3, 'chocolate banana surprise'
2,1, 'banana cream pie'
2,2, Failed
2,3, 'chocolate cream pie'
3,1, 'chocolate banana surprise'
3,2, 'chocolate cream pie'
3,3, Failed
Clairement cette requête ne gère pas le cas lorsque les deux contraintes sont les mêmes, mais fonctionne pour tous les autres cas.
Edit 4: de manutention(même contrainte cas):
remplacer cette ligne:
r.id = (select t1...
pour
r.id in (select t1...
fonctionne avec l'échec de cas à donner:
1,1, 'banana cream pie' and 'chocolate banana surprise'
2,2, 'chocolate cream pie' and 'banana cream pie'
3,3, 'chocolate cream pie' and 'chocolate banana surprise'