Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2010
    Posts
    1

    Unanswered: Help joining tables

    Have tables
    category (id, name), recipe(id, name), ingredient(id, name),
    reccat(id, recipeid, categoryid), recing(id, recipeid, ingredientid)

    Have working html/php form that returns choices such as
    categoryid=1, categoryname=appetizer
    ingredientid=6, ingredientname=onions, etc.

    want sql to find recipename associated with appetizer and onions. not sure how to proceed.

    thanks for any assistance,
    roy

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT recipe.name
      FROM ( SELECT reccat.recipeid
               FROM category
             INNER
               JOIN reccat
                 ON reccat.categoryid = category.id
              WHERE category.name = 'appetizer' ) AS appetizers
    INNER
      JOIN ( SELECT recing.recipeid
               FROM ingredient
             INNER
               JOIN recing
                 ON recing.ingredientid = ingredient.id
              WHERE ingredient.name = 'onions' ) AS onions
        ON onions.recipeid = appetizers.recipeid
    INNER
      JOIN recipe
        ON recipe.id = onions.recipeid
    please note in your reccat and recing tables, the id columns are useless and counterproductive
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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