So I’m a total newbie trying to solve this exercise where I have to find all the dishes that are marked as Vegetarian but contain Turkey meat in their ingredients.
This is what I’ve tried (this is where I inner join 3 tables to find the ingredients):
SELECT Name FROM Dishes INNER JOIN DishesIngredients ON DishesIngredients.DishId = s.Id INNER JOIN Ingredients ON DishesIngredients.IngredientID = Ingredients.ID
this is where I can’t seem to be able to join the subquery to identify the Vegetarian tag:
WHERE Ingredients.Name = 'Turkey meat' = (SELECT Name FROM Tags INNER JOIN DishesTags ON DishesTags.TagID = Tags.ID INNER JOIN Dishes ON DishesTags.DishID = Dishes.ID)
The diagram of the database is here for reference:
Advertisement
Answer
Let first find out how many dishes have Turkey meat as ingredient.
You have:
SELECT D.ID FROM Dishes D JOIN DishIngredients DI ON D.ID = DI.DishID JOIN Ingredients I ON DI.IngredientID = I.ID WHERE I.Name LIKE 'Turkey meat'
Then get all dishes with tag ‘Vegetarian’.
SELECT D.ID FROM Dishes D JOIN DishIngredients DI ON D.ID = DI.DishID JOIN Ingredients I ON DI.IngredientID = I.ID JOIN DishesTags DT on D.ID = DT.DishID JOIN Tags T ON DT.TagID = T.ID WHERE I.Name LIKE 'Turkey meat' AND T.Name = 'Vegetarian'