gpt4 book ai didi

mysql - 使用 Join with Where

转载 作者:可可西里 更新时间:2023-11-01 07:20:12 26 4
gpt4 key购买 nike

我有下表。

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(例如)

最佳答案

如果我理解您的问题,我认为您不需要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'
)

关于mysql - 使用 Join with Where,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/27508949/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com