JOIN returns only one column correct [Archive]

View Full Version : JOIN returns only one column correct

phpPete

03-09-02, 14:04

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

Desired result is: recipe_ingredient (from recipe_ingredient ) and 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 A, prep B
LEFT JOIN menu_item M USING ( 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;

-----------------------------------------------------

Paul

03-09-02, 17:26

It looks like the prep table is not being joined in. Try this:

SELECT A.recipe_ingredient AS Ingredient,
B.instruction AS 'Prep Note'
FROM recipe_ingredient A,
prep B,
menu_item M
WHERE M.menu_item_id = A.menu_item_id
AND M.menu_item_id = B.menu_item_id
AND M.menu_item_name = ' Some Name ' ;

phpPete

03-10-02, 08:46

Thanks for your answer, unfortunately, I'm still not getting the desired results, which begs the question: is the DB design flawed at this point?

phpPete

03-12-02, 11:37

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 )

=====================
And this query return what I'm seeking:
=====================
SELECT RI.ingredient AS 'INGREDIENT ', P.instruction AS 'PREP NOTE'

FROM recipe_ingredient RI, prep P, menu_item M

LEFT JOIN recipe R ON M.item_name = R.rec_name

WHERE M.item_name = 'Chicken Fingers'

AND R.rid = RI.rid
AND P.pid = R.pid

GROUP BY
RI.ingredient

Thanks for the response.