CODEWITHSUNDEEP
mysql
Nick
Nick

Reputation: 9373

Mysql looping through temporary table

I am trying to set a query result to a variable and then use that result in another query.

So right now I have several type of Whiskeys in my ingredients table and I can find all of them and with:

CREATE TEMPORARY TABLE TempTable (Ingredient_Id int); 
Insert into TempTable Select Ingredient_Id from ingredients where INSTR(Ingredient_Name,'Whiskey')>0;

Which gives me all my id's that I need. Then I am trying to get the relevant data with:

select drink_names.*,ingredients.*, drink_recipes.*
from drink_recipes
Left JOIN drink_names ON drink_recipes.Drink_Id = drink_names.Drink_Id
Left JOIN ingredients ON ingredients.Ingredient_Id = drink_recipes.Ingredient_Id
where drink_recipes.Ingredient_Id in TempTable #This is the problem
Order By Drink_Name

I am at a loss of how to run this query with each id in the TempTable

Upvotes: 0

Views: 1081

Answers (1)

juergen d
juergen d

Reputation: 204746

select drink_names.*,ingredients.*, drink_recipes.*
from drink_recipes
Left JOIN drink_names ON drink_recipes.Drink_Id = drink_names.Drink_Id
Left JOIN ingredients ON ingredients.Ingredient_Id = drink_recipes.Ingredient_Id
where drink_recipes.Ingredient_Id in 
(
  Select Ingredient_Id 
  from ingredients 
  where INSTR(Ingredient_Name,'Whiskey')>0
)
Order By Drink_Name

or you can also try

select drink_names.*,ingredients.*, drink_recipes.*
from drink_recipes
Left JOIN drink_names ON drink_recipes.Drink_Id = drink_names.Drink_Id
Left JOIN ingredients ON ingredients.Ingredient_Id = drink_recipes.Ingredient_Id
                      AND INSTR(ingredients.Ingredient_Name,'Whiskey') > 0
Order By Drink_Name

Upvotes: 1

Related Questions