Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39

    Unanswered: 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
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    It looks like the prep table is not being joined in. Try this:
    Code:
    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 ' ;

  3. #3
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39
    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?

  4. #4
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39
    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.

Posting Permissions

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