phpPete
03-06-02, 13:09
| 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 |