Find records in join table that only have a single row

Question

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.

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

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

Answer 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

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

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

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.

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