Querying of same columns with different values

Question

Im just new to MySql Programming and Im creating a project to enchance my skills. I have a table called tblRecipe which has the columns RecipeId, RecipeName and Instructions. the other table is called tblIngredients which has the columns IngredientId,RecipeId, IngredientName.

Now for example,RecipeOne needs IngredientOne and IngredientTwo to make, RecipeTwo needs IngredientOne,IngredientTwo and IngredientThree to make. My programs asks the IngredientNames for the input so when i put in IngredientOne and IngredientTwo it should give me back the result RecipeOne.

Here is my code

Select Recipename,Instructions 
from tblREcipe where RecipeId in 
         (select recipeID 
         from tblIngredients 
         where Ingredientname in('IngredientOne','IngredientTWo')
         );

I know that the IN operator is like saying match any of the following. So with that code it gives me both recipes. Im looking for an equivalent of AND. I tried "Ingredientname =ALL(in('IngredientOne','IngredientTwo'))" but it does not return any rows.

Im open to any changes such as restructuring the tables if that fixes the problem since this is just a practice project.Hope to hear from you soon guys and thank you in advance.

John Woo · Accepted Answer · 2012-08-10 08:56:16Z

1

You also need to count the number of instance of the records that match to the number of ingredients. Like this one below:

Select a.Recipename, 
       a.Instructions 
FROM   tblREcipe a INNER JOIN
         (
             SELECT   recipeID,
                      COUNT(IngredientId)
             FROM     tblIngredients 
             WHERE    Ingredientname IN ('IngredientOne', 'IngredientTWo')
             GROUP BY recipeID
             HAVING   COUNT(IngredientId) = 2    -- the number of ingredients
                                                 -- should somehow dynamic
         ) b ON a.recipeID = b.recipeID

edited Aug 10, 2012 at 8:56

answered Aug 10, 2012 at 8:47

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Querying of same columns with different values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related