1

I have problem with take specific data:

I have 5 tables:

  1. recipes {id_recipe, recipe_name, recipe}
  2. ingredients {id_ingredient, ingredient_name, ingredient_calories}
  3. categories {id_category, category_name}
  4. ingredients_recipes {id_recipe, id_ingredient, ingredient_weight}
  5. categories_recipes {id_recipe, id_category}

Now, I want to get:

id_recipe | recipe_name | recipe | ingredient_name | category_name 

looking by specific ingredients, let's say I want this data for recipe which contains:

id_ingredient = 1 AND id_ingredient = 10 AND id_ingredient = 15

The output i'm looking for should looks like this

| id_recipe | recipe_name  | recipe          | category_name | ingredient_name      |
|-----------|--------------|-----------------|---------------|----------------------|
| 1         | First recipe | Do it like this | 1st Category  | 1 Ingredient (Id=1)  |
|           |              |                 | 2nd Category  | 2 Ingredient (Id=10) |
|           |              |                 |               | 3 Ingredient (Id=15) |
|           |              |                 |               | 4 Other ingredient   |
|           |              |                 |               | 5 Other ingredient   |

It is quite easy to do it with OR except AND but then I get the recipes wchich don't contains all of looking ingredients in one time. I also want to have possibility to add category to Query on the same rules like:

AND id_category = x

Maybe I wrong constructed tables and now my tries are for nothing, I'm beginner in MySQL and programming and it is quite complicate to me.

Do you have any suggestion how to do that or at least where I need to start looking to get to that point ?


I tried to add to this category filter but it fails, i'm not sure why:

       (select id_recipe from  +
            ( +
            select id_recipe,  +
            sum(case when id_ingredient = 1 then 1 else 0 end) i1,  +
            sum(case when id_ingredient = 2 then 1 else 0 end) i2,  +
            sum(case when id_ingredient = 3 then 1 else 0 end) i3  +
            from ingredients_recipes  +
            group by id_recipe) t1,  +
            ( +
            select id_recipe,  +
            sum(case when id_category = 1 then 1 else 0) c1  +
            from categories_recipes  +
            group by id_recipe) t2  +
            where i1+i2+i3 = 3 AND c1 = 1 AND t1.id_recipe = t2.id_recipe);
2
  • 1
    Is there any relation between Categories and Ingredients?
    – McNets
    Commented May 9, 2017 at 12:43
  • There is no direct relationship between this two. Ingredients and categories are assigned to particular recipes only.
    – Wozniaxos
    Commented May 9, 2017 at 17:04

3 Answers 3

1

From an application standpoint, I think you'd be much better off doing this as several steps, not a single query result:

Step 1: Find recipes that match your criteria

SELECT id_recipe, recipe_name, recipe
FROM recipes
WHERE id_recipe IN (SELECT id_recipe, COUNT(*) 
                    FROM ingredients_recipes
                    WHERE id_ingredient IN (1, 10, 15)
                    GROUP BY id_recipe
                    HAVING COUNT(*) = 3)

There are probably a bunch of ways to do this. I chose to basically count the number of matching ingredients each recipe includes (via the IN clause), and only taking those that match all 3 ingredients.

This works because the WHERE clause is applied before the GROUP BY, and the HAVING is applied after.

At this point you can display the list of matching recipes to the user, and when they select one, you go on to the next step:

Step 2: go back and use id_recipe to retrieve the rest of the information

To be honest, there is a 90% chance I'd do 3 separate queries, depending on how exactly my application is displaying it. That is much easier than mashing into one query, then trying to parse out all the different pieces back on the application side.

First you'll need the recipe details (or just used your cached results from the prior query):

SELECT id_recipe, recipe_name, recipe
FROM recipes
WHERE id_recipe = %whatever%

Then get your categories:

SELECT cr.id_category, c.category_name
FROM categories_recipes cr
INNER JOIN categories c
ON cr.id_category = c.id_category
WHERE cr.id_recipe = %whatever%

This doesn't have anything to do with your list of ingredients, so display these categories under your recipe description.

Then get your ingredients:

SELECT id_recipe, id_ingredient, ingredient_weight
FROM ingredients_recipes
WHERE id_recipe = %whatever%
0

Interesting problem. The normal way of using IN really means "any of these". The following would get annoying with lots of ingredients, but you could get the answer you're looking for like so:

select id_recipe from 
(
select id_recipe,
sum(case when id_ingredient = 1 then 1 else 0 end) i1,
sum(case when id_ingredient = 10 then 1 else 0 end) i2,
sum(case when id_ingredient = 15 then 1 else 0 end) i3
 from ingredients_recipes
group by id_recipe ) t
where i1+i2+i3=3

This is evidently called relational division and isn't typically implemented. This post has more discussion: https://stackoverflow.com/questions/4751361/using-mysql-in-clause-as-all-inclusive-and-instead-of-or

2
  • Oh, it's finding right id'ies of recipes which contains this ingredients, so after from statement it don't need to be name of the table ? this select Subquery isn't return the name of a table i guess. Sorry for maybe dumb question :)
    – Wozniaxos
    Commented May 9, 2017 at 12:53
  • I am not sure I understand the question. The query I wrote would return a list of ID_RECIPE values that met your original criteria. I didn't include all the columns you specified because I was attempting to keep it simple, but you could join the outer table, t, to recipes and categories_recipes for that.
    – CalZ
    Commented May 9, 2017 at 14:09
-2
id_ingredient = 1 AND id_ingredient = 10 AND id_ingredient = 15

You need OR, not AND. Another way to say it:

id_ingredient IN (1, 10, 15)

The formatting of the blank cells is best done in application code, not SQL.

3
  • 1
    I don't think IN will work. He doesn't want recipes containing any of those ingredients, he wants recipes with all of those ingredients.
    – CalZ
    Commented May 9, 2017 at 17:08
  • @CalZ - The ANDs as written will always produce the empty set.
    – Rick James
    Commented May 9, 2017 at 18:35
  • I never said they would work. I was saying your suggestion of IN won't work :)
    – CalZ
    Commented May 10, 2017 at 1:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.