where clause sub query using avg

Question

I have 2 tables, food and score.

Food

• id
• name
• picture

score

• id
• score
• food_id.

I want to query food that have average score above 7:

select * from food where (select avg(score) from score group by food_id)>=7

But this returns

error #1242 - Subquery returns more than 1 row.

Answer 1

Try out this:

select fd.* from food fd
inner join score sc on fd.id=sc .food_id
group by food.id
having avg(sc.score)>=7

Answer 2

Try this

  SELECT * FROM food f INNER JOIN score s
  ON f.id = s.food_id
  WHERE avg(s.score)>=7
  GROUP BY f.food_id