SitePoint Sponsor |
|
User Tag List
Results 1 to 7 of 7
-
Mar 9, 2002, 13:14 #1
- Join Date
- Feb 2002
- Location
- Atlanta, GA
- Posts
- 342
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
JOIN returns only one column correct
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;
-
Mar 9, 2002, 15:39 #2
- Join Date
- Jan 2001
- Location
- buried in the database shell (Washington, DC)
- Posts
- 1,107
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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:
Code: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.
Remember to index the columns used in the JOIN and WHERE clauses to keep performance high and avoid cartesian products (VeryBadThing!).Matt - Sybase DBA / PHP fanatic
Sybase/MySQL/Oracle | I don't like MySQL
Download Sybase | DBForums.com - for all your RDBMS talk
-
Mar 10, 2002, 02:57 #3
- Join Date
- Feb 2002
- Location
- Atlanta, GA
- Posts
- 342
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Close but no cigar
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
-
Mar 11, 2002, 07:40 #4
- Join Date
- Nov 1999
- Location
- Mechanicsburg, PA
- Posts
- 7,294
- Mentioned
- 123 Post(s)
- Tagged
- 1 Thread(s)
Re: JOIN returns only one column correct
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:
Code: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
Dave Maxwell - Manage Your Site Team Leader
My favorite YouTube Video! | Star Wars, Dr Suess Style
Learn how to be ready for The Forums' Move to Discourse
-
Mar 11, 2002, 08:07 #5
- Join Date
- Feb 2002
- Location
- Atlanta, GA
- Posts
- 342
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Mar 12, 2002, 09:00 #6
- Join Date
- Jul 2001
- Posts
- 86
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.Get ConMan and run your own web site!
Want free programming eBooks? http://www.devarticles.com/ebooks.php
-
Mar 12, 2002, 10:23 #7
- Join Date
- Feb 2002
- Location
- Atlanta, GA
- Posts
- 342
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Bookmarks