Odd behavior combining multiple tables and using COALESCE

Question

I have a big query that I have been struggling with and tweaking for awhile.

SELECT 
    tastingNotes.userID, tastingNotes.beerID, tastingNotes.noteID, 
    tastingNotes.note, user.userName, 
    COALESCE(sum(tasteNoteRate.score),0) as `score`
FROM 
    tastingNotes
    INNER JOIN `user` on tastingNotes.userID = `user`.userID 
    LEFT JOIN tasteNoteRate on tastingNotes.noteID = tasteNoteRate.noteID  
WHERE tastingNotes.beerID = 'C5RJc0'
GROUP BY tastingNotes.noteID
ORDER BY score DESC
LIMIT 0,50;

I am using the COALESCE(sum(tasteNoteRate.score),0) to give results returned a value of zero if they do not have a score yet.

The odd behavior was that when I should have had two results it only returned one note with a score of zero.

When I then gave one a score they then both showed up, one with its score and then the second with zero.

Answer 1

Try

SELECT q.noteID, q.userID, q.beerID, q.note, q.score, u.userName
  FROM (
  SELECT n.noteID, n.userID, n.beerID, n.note, COALESCE(SUM(r.score), 0) score
    FROM tastingNotes n LEFT JOIN tasteNoteRate r
      ON n.noteID = r.noteID
   WHERE n.beerID = 'C5RJc0'
   GROUP BY n.noteID, n.userID, n.beerID, n.note
) q JOIN `user` u ON q.userID = u.userID
ORDER BY score DESC
LIMIT 50

SQLFiddle