SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Reporting all recipes containing certain amounts of some ingredients

    Hi,

    I'm trying to build code to get recipe codes from those recipes containing some quantities of some ingredients.

    table is:
    recipe_code | recipe_name | ingredient | ingredient_quantity


    I'd like to get all recipes containing between 100 and 200 grams of rice and, at the same time, between 1 and 2 grams of salt; but only those recipes fulfilling both requirements at the same time.

    I'd appreciate your help.

    Atzen

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you actually repeat the recipe code and recipe name for every single ingredient in a recipe?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In the case you presented the request is: "SELECT recipe_code FROM table_name WHERE (ingredient=rice AND ingredient_quantity BETWEEN 100 AND 200) AND (ingredient=salt AND ingredient_quantity BETWEEN 1 AND 2)

    More indicated is to have 3 tables:
    table1: recipes
    recipe_code|recipe_name
    table2: ingredients
    ingredient_code|ingredient_name
    table3: recipes_ingredients
    recipe_code|ingredient_code|ingredient_quantity

    In this case the request is: "SELECT r.recipe_code FROM recipes r, ingredients i, recipes_ingredients ri WHERE (r.recipe_code=ri.recipe_code AND r.ingredient_code=ri.ingredient_code) AND (i.ingredient_name='rice' AND ri.ingredient_quantity BETWEEN 100 AND 200) AND (i.ingredient_name='salt' AND ingredient_quantity BETWEEN 1 AND 2)"

  4. #4
    SitePoint Member
    Join Date
    Jan 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you actually repeat the recipe code and recipe name for every single ingredient in a recipe?
    Yes, I do.
    Actually what I have is a table resulting from the union of the three tables suggested by groody_son. I thought it'd be easier that way.

    Thanks groody_son, I'll try your second approach.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by groody_son View Post
    WHERE ...
    AND (i.ingredient_name='rice' AND ri.ingredient_quantity BETWEEN 100 AND 200)
    AND (i.ingredient_name='salt' AND ingredient_quantity BETWEEN 1 AND 2)"
    this will ~always~ return zero rows

    the WHERE clause is evaluated on each row separately -- and there's no way on earth that the same ingredient_name value can be equal to two separate things at the same time

    what's needed here is GROUP BY and HAVING
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, you're right, sorry for that mistake, i put a query not tested:

    So, here are the tables:
    table1: recipes
    recipe_code|recipe_name
    table2: ingredients
    ingredient_code|ingredient_name
    table3: recipes_ingredients
    recipe_ingredient_code|recipe_code|ingredient_code|ingredient_quantity

    ...and here is the query, tested and working:
    SELECT r.recipe_code FROM recipes r, ingredients i, recipes_ingredients ri WHERE (r.recipe_code=ri.recipe_code AND i.ingredient_code=ri.ingredient_code) AND (i.ingredient_name='salt' AND ri.ingredient_quantity BETWEEN 1 AND 2) AND r.recipe_code IN (SELECT r.recipe_code FROM recipes r, ingredients i, recipes_ingredients ri WHERE (r.recipe_code=ri.recipe_code AND i.ingredient_code=ri.ingredient_code) AND (i.ingredient_name='rice' AND ri.ingredient_quantity BETWEEN 100 AND 200))

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by groody_son View Post
    So, here are the tables:
    table3: recipes_ingredients
    recipe_ingredient_code|recipe_code|ingredient_code|ingredient_quantity
    i prefer without the recipe_ingredient_code column

    and the query is like this --
    Code:
    SELECT r.recipe_code 
      FROM recipes AS r
    INNER
      JOIN recipes_ingredients AS ri
        ON ri.recipe_code = r.recipe_code
    INNER
      JOIN ingredients AS i
        ON i.ingredient_code = ri.ingredient_code
     WHERE ( i.ingredient_name='salt' AND ri.ingredient_quantity BETWEEN 1 AND 2 )
        OR ( i.ingredient_name='rice' AND ri.ingredient_quantity BETWEEN 100 AND 200 )
    GROUP
        BY r.recipe_code 
    HAVING COUNT(*) = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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