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

    Unanswered: 3 table query. Use JOIN or Temp Table??

    In order to derive a working cost for an item I need to query 3 tables.

    My structure is as follows:


    //Costed items table ( available ingredients )

    Field Type Null Key Default Extra
    ----- ---- ---- --- ------- -----
    cost_item_id int(11) PRI NULL auto_increment
    cost_item_name varchar(30)
    gross_cost double 0
    menu_item_id varchar(12)


    ******
    //Recipe table

    Field Type Null Key Default Extra
    ----- ---- ---- --- ------- -----
    recipe_id int(11) PRI NULL auto_increment
    ingredient varchar(30)
    portion_type varchar(10)
    portion_amt double 0
    instruction varchar(50) YES NULL
    menu_item_id varchar(12) MUL

    *****
    //Measurement table

    Field Type Null Key Default Extra
    ----- ---- ---- --- ------- -----
    mid int(11) PRI NULL auto_increment
    m_type varchar(15)
    m_conv float 0

    *****
    Current Query Version

    SELECT ingredient AS Ingredient,
    portion_amt as Unit,
    ((recipe.portion_type * recipe.portion_amt) * cost_item.gross_cost) as 'Cost This Menu Item'
    FROM
    cost_item, recipe
    WHERE recipe.ingredient = cost_item.cost_item_name

    This query will give me all I need, however, within measurement column m_type are values such as Ounce, pound etc.

    For formatting purposes I'd like to include these for the results.
    My desired output would look like:

    Unit Amount Ingredient Cost This Menu Item

    4 ounce chicken 1.23


    So, should I use a join here or build a temporary table?

    Also, any suggestions about further abstracting these tables are appreciated

    As Always,

    Pete

  2. #2
    Join Date
    Feb 2002
    Location
    New Jersey
    Posts
    39

    Disregard this question

    Well,

    decided to further normalize the DB so this question no longer pertinent.

    Thanks anyway,

    Pete

Posting Permissions

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