CODEWITHSUNDEEP
mysql
Jim Vercoelen
Jim Vercoelen

Reputation: 1077

Mysql how to select SUM based on column other table

Working on this app where I need make a query which seems to be impossible (after hours trying and trying).

Although, I have to admit, I am a bit rusty on the queries!

Anyway, lets assume there are these 3 tables:

Meal

id name
-- ----  
1  meal1

Food

id name  kcal kj
-- ----  ---- --
1  food1 200  200
2  food2 300  300

Meal_food

m_id f_id percentage_food_in_meal
---- ---- ----
1    1    0.60
1    2    0.40

What I am trying to select is: a meal, with the kcal, kj (and others) belonging to this meal based on the percentage.

As I tried, I already tried, but I can show you the closest I can get:

SELECT 
  m.*, 
  SUM(f.kj),    -- <- should be based on the percentage
  SUM(f.kcal)   -- <- should be based on the percentage
FROM 
  meal m, 
  meal_food mf, 
  food_nl f
WHERE 
  m.id = mf.meal_id
AND 
  f.id = mf.food_id
GROUP BY 
  m.id;

As the comment says: the based on the percentage is missing. Tried inner joins, outer joins, cross joins, sub queries, so I am wondering if it's even possible.

Hopefully my question about what I am trying to get is clear, if not I would be happy to try it again!

Update For example as output:

id name  kcal kj
-- ----  ---- --- 
1  meal1 240  240

To clarify: meal1 consists of 2 food (ingredients):

Upvotes: 0

Views: 43

Answers (1)

PeterHe
PeterHe

Reputation: 2766

Not tested:

SELECT 
  m.id, MIN(m.name) AS meal_name, 
  SUM(f.kj*mf.percentage_food_in_meal),  
  SUM(f.kcal*percentage_food_in_meal)
FROM meal m 
INNER JOIN  meal_food mf
ON m.id = mf.meal_id
INNER JOIN food_nl f
ON f.id = mf.food_id
GROUP BY m.id;

Upvotes: 1

Related Questions