Учитывая следующие таблицы:
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
Как я создаю SQL-запрос для нахождения рецептов где ingredients.name = 'шоколад' и ingredients.name = 'сливки'?
Это называется реляционным делением. Различные методы обсуждаются здесь .
Еще одна не приведенная альтернатива - двойное НЕ СУЩЕСТВУЕТ
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))
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 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
Ключевым моментом здесь является то, что количество должно равняться количеству названий ингредиентов. Если это не точный счетчик, существует риск ложных срабатываний из-за дубликатов.
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' )
Отредактировал следующий комментарий, спасибо! Тогда это правильный путь:
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'
другим способом:
Версия 2 (как хранимая процедура) исправлена
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)
Редактировать 2: [прежде, чем люди начнут кричать]:)
Это может быть помещено в начало версии 2, что позволит запрашивать по имени вместо передачи идентификатора.
select @recipeId1 = recipe_id from Ingredients where name = @Ingredient1
select @recipeId2 = recipe_id from Ingredients where name = @Ingredient2
Я тестировал версию 2, и она работает. Большинство пользователей, которые использовали ссылку на таблицу ингредиентов, в этом случае были совершенно не нужны!
Редактировать 3: (результаты теста);
Когда эта хранимая процедура запускается, это результаты.
Результаты имеют формат (First Recipe_id; Second Recipe_id, Result)
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
Очевидно, что этот запрос не обрабатывает случай, когда оба ограничения одинаковы, но работает для всех других случаев.
Редактировать 4: (обработка того же случая ограничения):
замена этой строки:
r.id = (select t1...
на
r.id in (select t1...
работает с неудачными случаями, чтобы дать:
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'
Если вы ищете несколько ассоциаций, то самый простой способ написать запрос - использовать несколько условий EXISTS
вместо одного прямого 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'
)
Если вы точно знаете, что ассоциации уникальны (т.е. в одном рецепте может быть только один экземпляр каждого ингредиента), то вы можете немного схитрить, используя группирующий подзапрос с функцией COUNT
и, возможно, ускорить процесс (производительность зависит от СУБД):
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
Или, если в рецепте может быть несколько экземпляров одного и того же ингредиента (нет ограничения UNIQUE
в таблице ассоциации RecipeIngredients
), вы можете заменить последнюю строку на:
HAVING COUNT(DISTINCT i.name) = 2