在连接表中查找只有一行的记录

Table

recipe_food_xref
recipe_id int
food_id int

Need to find the one record in recipe_food_xref where the recipe has only one food and that food is a specific food.

需要找到recipe_food_xref中的一条记录,其中食谱只有一种食物,食物是特定食物。

It works perfectly joining to itself:

它完美地融入了自身:

SELECT x1.recipe_id FROM recipe_food_xref x1
INNER JOIN recipe_food_xref x2 ON x2.recipe_id = x1.recipe_id
WHERE x1.food_id = 1
GROUP BY x1.recipe_id
HAVING COUNT(x2.recipe_id) = 1

That seems a bit ugly and I'd like to know if there's a better way.

这看起来有点难看,我想知道是否有更好的方法。

Here's a SqlFiddle with some sample data. Basically I want to find recipe_id:1 since it has food_id:1 and does not have more than one food_id

这是一个包含一些示例数据的SqlFiddle。基本上我想找到recipe_id:1因为它有food_id:1并且没有多个food_id

http://sqlfiddle.com/#!3/6d474/1

4 个解决方案

#1

SELECT recipe_id 
FROM recipe_food_xref
GROUP BY recipe_id
HAVING sum(case when food_id = 1 then 1 else 0 end) = 1
and sum(case when food_id <> 1 then 1 else 0 end) = 0

SQLFiddle demo

#2

This was my first go:

这是我第一次去:

SELECT recipe_id
FROM recipe_food_xref
GROUP BY recipe_id
HAVING COUNT(food_id) = 1 AND SUM(food_id) = 1;

Note that the general format is HAVING COUNT(FOOD_ID) = 1 AND SUM(FOOD_ID) = <food id>. It works because, if there is only one food_id, then the sum is equal to the id.

请注意,一般格式为HAVING COUNT(FOOD_ID)= 1 AND SUM(FOOD_ID)= 。它的工作原理是,如果只有一个food_id,则总和等于id。

Seems similar to Juergen's answer but simpler. Did I miss something?

似乎与Juergen的答案类似,但更简单。我错过了什么?

I also tried the following, which has to scan the table extra times but as I'd never used the ALL keyword before so I thought it was interesting.

我也尝试了以下内容,它必须多次扫描表格,但因为我之前从未使用过ALL关键字,所以我觉得它很有趣。

SELECT recipe_id
FROM recipe_food_xref AS r
WHERE 1 = ALL (SELECT food_id FROM recipe_food_xref WHERE r.recipe_id=recipe_id);

http://sqlfiddle.com/#!3/6d474/26

#3

select *
from recipe_food_xref x
where not exists (
                   select i.food_id
                   from recipe_food_xref i
                   where i.recipe_id = x.recipe_id and
                         i.food_id <> x.food_id
                 ) 
-- if this is omitted you get all recipes having just one food:
      and x.food_id = 1

#4

Here's my interpretation of the problem:

这是我对问题的解释:

Find all recipes with a single ingredient. Of these recipes, find the one whose single ingredient is X

查找所有含有单一成分的食谱。在这些食谱中,找到单一成分为X的食谱

SELECT recipe_id
     , Max(food_id) As food_id
     , Count(*) As number_of_ingredients
FROM   recipe_food_xref
GROUP
    BY recipe_id
HAVING Count(*) = 1
AND    Max(food_id) = 1