Thursday, 30 August 2018

LEFT JOIN Does not return the correct result

I have two table in my database

team
id game_id name image

26   48     t    t.png
27   48     t2   t2.png

score
id  team_id  score

1   26       5
2   26       14

my query
SELECT t.id,t.name,t.image,sum(s.score)
  FROM `team` AS t
  LEFT JOIN score s ON (s.team_id=t.id) where t.game_id=48

my query always give one team that is team 26
but it will be like
id  name image     score

26  t    t.png     19
27  t2   t2.png    null

I cant understand what wrong in my query.

Add a GROUP BY clause. Try this query.
SELECT t.id, t.name, t.image, sum(s.score) as total_score
FROM `team` AS t
LEFT JOIN `score` s ON (s.team_id = t.id)
WHERE t.game_id=48
GROUP BY t.id, t.name, t.image

0 comments:

Post a Comment