Did you try this?
SELECT
sum(fooditems.carb * mealitems.quantity) as sumcarbs,
sum(fooditems.gi * ((fooditems.carb * mealitems.quantity) /
sum(fooditems.carb * mealitems.quantity))),
sum(fooditems.gl * mealitems.quantity),
sum(fooditems.cal * mealitems.quantity)
FROM meals
INNER JOIN mealitems ON meals.meal_id = mealitems.meal_id
INNER JOIN fooditems ON mealitems.fooditem_id = fooditems.fooditem_id
WHERE meals.user_id = '$user_id' AND meals.date = '$meal_date'";
-Micah
On 09/05/2008 03:41 PM, Brian Dunning wrote:
I'm trying to calculate glycemic index of all food items eaten in a
day with a single SELECT. The problem is the calculation for glycemic
index of each item requires a total of all items' carbs. It's like a
circular reference. Here's what I'm trying:
SELECT
sum(fooditems.carb * mealitems.quantity) as sumcarbs,
sum(fooditems.gi * ((fooditems.carb * mealitems.quantity) /
sumcarbs)),
sum(fooditems.gl * mealitems.quantity),
sum(fooditems.cal * mealitems.quantity)
FROM meals
INNER JOIN mealitems ON meals.meal_id = mealitems.meal_id
INNER JOIN fooditems ON mealitems.fooditem_id = fooditems.fooditem_id
WHERE meals.user_id = '$user_id' AND meals.date = '$meal_date'";
It replies "Unknown column 'sumcarbs' in 'field list'". See what I'm
trying to do? Is there a way to do this?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
