mysql - 使用 Join with Where

Question

我有下表。

Table Name: Recipe
id  topic    description    difficultylevel     totaltime
1   Cheese   Cheese         Easy                10
2   Cheese1  Cheese1        Medium              50

Table Name: Ingredients
id  recipeid    ingredient
1   1           Butter
2   1           Cheese
3   1           Bread

现在，当我运行以下查询时，它会返回食谱 id = "1"，尽管它不应该返回，因为我不想要一个含有黄油的食谱。

SELECT recipe.id
FROM 
  `recipe`
  INNER JOIN ingredients ON recipe.id = ingredients.recipeid
WHERE 
  (recipe.description LIKE '%CHEESE%' OR recipe.topic LIKE '%CHEESE%')
  AND (recipe.difficultylevel='Easy')
  AND (recipe.totaltime <= 30)
  AND (ingredients.ingredient <> 'Butter')

因为 Cheese 和 Bread，它返回 recipe.id = "1"两次。我需要修复查询，以便它完全排除 recipe.id = "1"如果它有 Butter(例如)

Answer 1

如果我理解您的问题，我认为您不需要inner join。您可以使用带有 null 检查的 outer join，或者您可以使用 not exists:

select id
from recipe r
where 
    (recipe.description LIKE '%CHEESE%' OR recipe.topic LIKE '%CHEESE%')
    AND (recipe.difficultylevel='Easy')
    AND (recipe.totaltime <= 30)
    AND not exists (
        select 1
        from ingredients i
        where r.id = i.recipeid
             and i.ingredient = 'Butter'
    )