SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    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;


  2. #2
    Database Jedi MattR's Avatar
    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.
    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!).

  3. #3
    SitePoint Addict
    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


  4. #4
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,267
    Mentioned
    116 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
    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...)
    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

  5. #5
    SitePoint Addict
    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


  6. #6
    SitePoint Enthusiast
    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

  7. #7
    SitePoint Addict
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •