SQL Query with a join to return items with only one type of linked field from other table (Top N)

Question

Imagine a recipe database. There are three tables:

tbl_recipes, tbl_recipe_ingredients and tbl_ingredients.

Ingredients can be vegetable or meat. (ingtype field)

I need to have two queries, one to pull all recipes that contains vegetable AND meat, and another for only vegetables.

I have been trying this query, but it's not returning accurate numbers of results.

SELECT DISTINCT tbl_recipes.title
FROM tbl_recipe_ingredients
INNER JOIN tbl_recipes ON tbl_recipe_ingredients.recipe_id = tbl_recipes.id
INNER JOIN tbl_ingredients ON tbl_recipe_ingredients.ingredient_id = tbl_ingredients.id
WHERE tbl_ingredients.ingtype = 'vegetable'
GROUP BY tbl_recipes.id
HAVING COUNT( DISTINCT tbl_ingredients.ingtype ) = 1

Another thing that's not working is that if I change the Count to = 2 (meaning one or more of each ingredient type), I get no results.

I think this is a variation of the Top N type queries, but I'm having trouble seeing where the issue is with this one.

I would appreciate any help.

Answer 1

To get those titles that only have vegetable, you could do something like this using COUNT with CASE:

SELECT DISTINCT tbl_recipes.title
FROM tbl_recipe_ingredients
   INNER JOIN tbl_recipes ON tbl_recipe_ingredients.recipe_id = tbl_recipes.id
   INNER JOIN tbl_ingredients ON tbl_recipe_ingredients.ingredient_id = tbl_ingredients.id
GROUP BY tbl_recipes.id
HAVING COUNT( CASE WHEN tbl_ingredients.ingtype = 'vegetable' THEN 1 END ) = COUNT(1)

This counts all the ingredient types equal to vegetable and compares to the overall count -- if those are the same, it's a match.

---

Then to get the ones that have both vegetable and meat, this should work:

SELECT DISTINCT tbl_recipes.title
FROM tbl_recipe_ingredients
   INNER JOIN tbl_recipes ON tbl_recipe_ingredients.recipe_id = tbl_recipes.id
   INNER JOIN tbl_ingredients ON tbl_recipe_ingredients.ingredient_id = tbl_ingredients.id
WHERE tbl_ingredients.ingtype IN ('vegetable','meat')
GROUP BY tbl_recipes.id
HAVING COUNT( DISTINCT tbl_ingredients.ingtype ) = 2

The WHERE clause in the 2nd query may not be needed -- depends on if you have other ingredient types. This counts the DISTINCT ingredient types to make sure it's equal to 2.