Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Jan 2018
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MySQL selecting from one to many relationship table

    Hello, I am a newbie in MySQL and I need some help selecting from a one to many relationship table. I have this table:
    recipe_ingredients
    +-----------+---------------+
    | recipe_id | ingredient_id |
    +-----------+---------------+
    | 1 | 1 |
    | 1 | 4 |
    | 2 | 1 |
    | 2 | 4 |
    | 2 | 2 |
    | 3 | 1 |
    | 3 | 3 |
    | 3 | 4 |
    | 3 | 2 |
    | 4 | 1 |
    | 4 | 2 |
    | 4 | 3 |
    +-----------+---------------+


    So I want to select recipe_id by ingredient_id's. But it should only select recipe_id's for which I have specified all the ingredients. For example if I input ingredient_id's (1,2,3) it should select recipe_id: 4, or If I input (1,2,3,4) It should select recipe_id: 1,2,3,4. After some googling I found this query:
    Code:
    SELECT DISTINCT recipe_id FROM recipe_ingredients WHERE ingredient_id IN(1,2) GROUP BY recipe_id HAVING COUNT(ingredient_id)=2;
    However this does not work as needed.
    If you could point me to some tutorials or where I could learn how to do something similar to this I would appreciate it.

  2. #2
    Master Coder sunfighter's Avatar
    Join Date
    Jan 2011
    Location
    Washington
    Posts
    7,643
    Thanks
    34
    Thanked 1,034 Times in 1,031 Posts
    This is an error of logic:
    For example if I input ingredient_id's (1,2,3) it should select recipe_id: 4, or If I input (1,2,3,4) It should select recipe_id: 1,2,3,4.
    Shouldn't ingredient_id's (1,2,3) yield both recipe_ids 4 and 3?
    Evolution - The non-random survival of random variants.
    Physics is actually atoms trying to understand themselves.

  3. #3
    New to the CF scene
    Join Date
    Jan 2018
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It should only yield recipe_id 4 because recipe_id 3 has ingredients: 1,2,3 AND 4(which we didn't specify in the IN clause).

  4. #4
    Master Coder sunfighter's Avatar
    Join Date
    Jan 2011
    Location
    Washington
    Posts
    7,643
    Thanks
    34
    Thanked 1,034 Times in 1,031 Posts
    I wont argue logic here, but this being a book of recipes and the only way I can find a recipe is if I know all the ingredients is so bad.

    Let's say you enter chicken, cheese, ham and your query does not show Cordon Bleu Chicken, because the recipe also calls for salt, pepper, lemon, mushrooms, etc. then it's worthless.
    Evolution - The non-random survival of random variants.
    Physics is actually atoms trying to understand themselves.

  5. #5
    New to the CF scene
    Join Date
    Jan 2018
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It might be a bad idea but this is what i'm trying to do. Also each ingredient has a type (main, spice, optional) and it will only search by the main ones. So in your case Cordon bleu chicken main ingredients should be chicken, ham, cheese and anything else that is essential to making that recipe. So the query should find it. Anyways, even if this is completely useless I'm still learning and would like to know how to do such thing.

  6. #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    4,169
    Thanks
    3
    Thanked 533 Times in 520 Posts
    The query you found will match recipes that contain the list of ingredients. (1,2) matches recipe_id 2,3,4. (1,2,3) matches recipe_id 3,4. (1,2,3,4) matches recipe_id 3.

    To match recipes that wholly consist of any combination of the listed ingredients, wouldn't you need to get a count of the total number of ingredients in a recipe and compare that with the number of ingredients matched? If there are x ingredients in a recipe and the IN (...) matches those x ingredients, include that recipe in the result set. The current integer value in the having clause needs to instead be calculated using a sub-query to get the total number of ingredients in a recipe. See the following -

    Code:
    SELECT recipe_id FROM recipe_ingredients t1
     WHERE ingredient_id IN(a_list_of_ingredient_ids)
     GROUP BY recipe_id
     HAVING COUNT(*) = (select count(*) from recipe_ingredients t2 where t1.recipe_id = t2.recipe_id group by t2.recipe_id)
    Finding out HOW to do something is called research, i.e. keep searching until you find the answer. After you attempt to do something and cannot solve a problem with it yourself, would be when you ask others for help.

  7. #7
    Senior Coder benanamen's Avatar
    Join Date
    Oct 2015
    Posts
    1,449
    Thanks
    4
    Thanked 147 Times in 142 Posts
    it should only select recipe_id's for which I have specified all the ingredients.
    @sunfighter is right, the logic is bad.

    It might be a bad idea but this is what i'm trying to do.
    So, you get expert advise that what you are doing is bad and you insist on doing it anyways?????
    Programming is logical, what you are doing is not.

    * Sometimes you just cant help these people.
    To save time, lets just assume I am almost never wrong.

    The XY Problem
    The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

    "This text has been encoded with ROT26. If you can read this you must have found a backdoor. Congratulations!"

  8. #8
    New to the CF scene
    Join Date
    Jan 2018
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Look, the reason I insist on doing it anyways is because I consider it learning. If the logic or the idea are bad and my project is going to work like **** or not at all, I will learn to not do it again. Might look like a waste of time to experts like you, but for me its learning and experience.

  9. #9
    New to the CF scene
    Join Date
    Jan 2018
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for the response and the explanation! I will test this out tomorrow.


 

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •