Skip to content
Advertisement

How to join queries with a subquery?

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:

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'
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement