JOIN returns only one column correct

[phpPete]

JOIN returns only one column correct
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;

[MattR]

Avoid using 'USING' as that is MySQL non-standard code. Someone who is not familliar with MySQL may not understand what you're trying to do.

In any rate, you're not joining the PREP table to the menu_item table.

Something like this may work better:

SELECT whatever
  FROM menu_item mi
 INNER JOIN recipe_ingredient ri ON mi.menu_item_id = ri.menu_item_id
 INNER JOIN prep p               ON mi.menu_item_id = p.menu_item_id
 WHERE ORDER BY ETC.

Also, what is the 'mid' column? You don't appear to be using it at all in any of your relations -- I suspect 'menu_item( menu_item_id )' would be a more proper primary key.

Remember to index the columns used in the JOIN and WHERE clauses to keep performance high and avoid cartesian products (VeryBadThing!).

[phpPete]

Thanks for your answer, however, I still get only what I want in the 'Ingredient' Column, the 'Prep note' column returns the same instruction for each row.

RE: mid, it's currently a row id - primary key. I'm not using it, that's correct.

I've noted to vocal camps regarding using this sort of row id as a primary key/foreign key.

One scholl say it should never be done, the other says it a logical way to relate tables, so I'm undecided as to whether to use it, and do the same to other tables, or simply relate my tables via another unique identifier, curently menu-item_id or recipe_id

Still querying away here in New Jersey.

As this query has proved troublesome I'm wondering if my structure is in need of re-design ( ? )

As always, any response/critique is greatly appreciated,

Pete

[DaveMaxwell]

The problem you're having is I don't see a way to map the prep instructions to the corresponding ingredient based on how your table is defined.

Should recipe_id be recipe_ingredient_id on the prep table perhaps? That would allow you to write your query like this:

SELECT RI.recipe_ingredient AS Ingredient,
       P.instruction AS 'Prep Note' 
  FROM recipe_ingredient RI, prep P, Menu_Item M 
 WHERE M.Menu_Item_Name = ' Some Name '
   AND M.Recipe_ID = RI.Recipe_ID
   AND RI.Recipe_Ingredient_ID = P.Recipe_Ingredient_ID
 ORDER BY Recipe_Ingredient_ID, prep_id

I would suggest really going over your table definition and making sure it meets your needs (I don't think it does, but I'm not 100% sure of what you're doing...)

[phpPete]

I had a feeling that was the problem, and am pretty much convinced now that re - defining the tables here is pretty much the way to go, as I really must map ingredient to instruction the individual menu-item.

I appreciate the answer,

Pete

[Mytch2001]

Hi,
Have you tried a right outer join instead? A right outer join returns null for the columns that don't exist in the second table, so some menus may not have recipies.

[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.