Here's the scenario: 2 tables:

[phpPete]

Here's the scenario:

2 tables:

table1 ( a lookup table ) 1:N
id
ingredient
gross_cost
------------------------
table2 ( one record per item )
id
rec_ingredient
rec_unit ( a number )
rec_portion ( a unit of measurement )
rec_code ( unique identifier )



Now, using various queries I can calculate values for table2 based on table1.

I need to be able to tally up all the table2 cost values which I derive from using table1 gross_cost value, then use that total as a new value for one final calculation.

I thought an join would do this but I'm thinking more and more it'll be a temp table.



Pete

 

[phpPete]

Solved my problem.

SELECT @total_cost:=sum(round(r.rec_unit * r.rec_portion * gross_cost * 100) /100) from
FROM recipe_ingredient_data AS r
LEFT JOIN ingredient AS i
ON i.ingredient = r.rec_ingredient
WHERE r.rec_code = 'id3c9cb1714';

SELECT r.rec_ingredient AS Ingredient, i.gross_cost AS 'Bulk Price',
round(r.rec_unit * r.rec_portion * gross_cost * 100) /100 AS 'Ingredient Cost',
round(r.rec_unit * r.rec_portion * gross_cost * 100 / @total_cost ) AS '% of total'
FROM recipe_ingredient_data AS r
LEFT JOIN ingredient AS i
ON i.ingredient = r.rec_ingredient
WHERE r.rec_code = 'id3c9cb1714' ORDER BY r.rec_ingredient;