JOIN problem: results only patially correct

[phpPete]

I can't seem to get the data I want from my tables.

Desired result is: recipe_ingredient (from recipe_ingredient ) paired with its corresponding prep instruction from instructions ( from prep )

I've tried any number of combinations of JOINS, the closest I get to what I want is using this JOIN:

SELECT A.recipe_ingredient AS Ingredient, B.instruction AS 'Prep Note'
FROM recipe_ingredient R, prep P, menu_item M
INNER JOIN recipe_ingredient ri ON mi.menu_item_id = ri.menu_item_id

INNER JOIN prep pr ON mi.menu_item_id = pr.menu_item_id
WHERE
M.menu_item_name = ' Some Name '

GROUP BY
A.recipe_ingredient

**This gives me the correct ingredients, however, not the corresponding prep instruction for that ingredient's DB value.

HELP, I'm GOING CRAZY!!!

Thanks,

Pete


The structure is:
#
# Table structure for table `menu_item`
#

CREATE TABLE menu_item (
mid int(11) NOT NULL auto_increment,
menu_item_id varchar(11) NOT NULL default '',
recipe_id varchar(11) NOT NULL default '0',
menu_item_name varchar(35) NOT NULL default '',
PRIMARY KEY (mid)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Table structure for table `prep`
#

CREATE TABLE prep (
prep_id int(4) NOT NULL auto_increment,
menu_item_id varchar(11) NOT NULL default '',
instruction varchar(50) NOT NULL default '',
recipe_id varchar(11) NOT NULL default '',
PRIMARY KEY (prep_id)
) TYPE=MyISAM;
# --------------------------------------------------------

#
# Table structure for table `recipe_ingredient`
#

CREATE TABLE recipe_ingredient (
recipe_ingredient_id int(11) NOT NULL auto_increment,
recipe_ingredient varchar(35) NOT NULL default '',
menu_item_id varchar(11) NOT NULL default '',
ingredient_id int(4) NOT NULL default '0',
recipe_id varchar(11) NOT NULL default '',
PRIMARY KEY (recipe_ingredient_id)
) TYPE=MyISAM;

 

[piti]

hi

well, i'm not sure if this is it, so give it a try

SELECT recipe_ingredient.recipe_ingredient, prep.instruction
FROM menu_item
INNER JOIN prep ON prep.menu_item_id = menu_item.menu_item_id
LEFT JOIN recipe_ingredient ON recipe_ingredient.menu_item_id = menu_item.menu_item_id
WHERE menu_item.menu_item_name = 'name'

btw:
do you need that redundancy in your tables (menu_item_id, recipe_id)?

 

[phpPete]

Actually I'll have a right outer join for that very reason as a query that my chefs can select.

What ultimately turned out to be my problem here was not so much the normalization, but rather I was overlooking the fact that for each ingredient and prep instruction multiple entries need to be stored, then later retrieved via a join table with foreign keys.

A simple oversight but not one I'll make again soon....

So at this stage it looks like my latest design will work, I hope:

menu_item
--------
mid
item_name

prep
-----
pid
instruction

recipe_ingredient
-----------------
rid
ingredient

recipe
------
rec_id
rec_name
rid ( FK )
mid ( FK )
pid ( FK )

Thanks for the response.