Intereting Posts
Достаточно ли параметров для предотвращения инъекций Sql? SQL объединяет несколько идентификаторов для создания идентификатора группы для дубликатов записей Помогите создать запрос YQL для поиска компаний Как убить процесс / запрос в DB2 Упорядоченный счет последовательных повторов / дубликатов SQL Query – как не включать некоторые результаты Отображать SQL-запросы в журнале с помощью Rails 4 Oracle 11g – цикл FOR, который вставляет только будни в таблицу? Разработка базы данных для отношений «многие ко многим» с ограничениями Самый эффективный способ подсчета числа строк таблицы Столбцы для строк в MS Access подключение sqlalchemy к MSAccess SQL All Possible Round Robin Комбинации между двумя таблицами Невозможно использовать функцию MATCH в запрошенном контексте Могу ли я использовать язык запросов гибернации для объектов, не сопоставленных с таблицей?

SQL-запрос через промежуточную таблицу

Учитывая следующие таблицы:

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-запрос, чтобы найти рецепты, где указаны ингредиенты.name = 'chocolate' и components.name = 'cream'?

Это называется реляционным делением. Здесь обсуждаются различные методы.

Еще одна альтернатива, которая еще не дана, – это двойной НЕ СУЩЕСТВУЮЩИЙ

 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.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 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 

Если вы ищете несколько ассоциаций, самый простой способ написать запрос – использовать несколько условий 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 ассоциации RecipeIngredients ), вы можете заменить последнюю строку следующим:

 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, что позволит запрашивать по имени вместо передачи в id.

 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'