Help joining tables

roy brown · #1 (**permalink**) 04-07-10, 14:12

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

r937 · #2 (**permalink**) 04-07-10, 14:28

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

Internet Services

Web Development

Digital Marketing

Consumer Tech