CODEWITHSUNDEEP
mysqlsql
M M Mahmudul Hassan
M M Mahmudul Hassan

Reputation: 67

LEFT JOIN Not return 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.

Upvotes: 0

Views: 73

Answers (4)

peter.petrov
peter.petrov

Reputation: 39437

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

Upvotes: 1

Uzi Balooch
Uzi Balooch

Reputation: 50

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 
group by t.id

Just add group by clause in it

Upvotes: 1

Lego_blocks
Lego_blocks

Reputation: 301

You have aggregated the scores, so what your query will do is to combine the the scores for your two teams, if you want to return it on 2 separated results. you must put GROUP BY t.id

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 GROUP BY t.id

Upvotes: 1

Suchit kumar
Suchit kumar

Reputation: 11859

Group by will solve your problem:

SELECT t.id,t.name,t.image,sum(s.score) 
FROM `team` AS t 
LEFT JOIN score s ON (s.team_id=t.id and t.game_id=48) 
GROUP BY t.id

Upvotes: 0

Related Questions