mysql left join does not return values

This seems like a fairly common question on here although I haven't been able to find a solution.

I have three tables, matchup, matchup_options, matchup_votes

On create, a matchup gets created, "n" matchup_options are created and matchup_votes are empty until a vote is added.

My query is only returning one value even though I have two matchup_options, NEITHER have anything in the matchup_votes table. (These should return 0 until theres an actual entry, which would start counting correctly)

SELECT matchup.matchupID, matchup_option.player_id, player.name, player.abr, count(matchup_vote.player_id) votes FROM matchup
            INNER JOIN matchup_option ON matchup_option.matchupID= matchup.matchupID
            INNER JOIN player ON player.player_id = matchup_option.player_id
            LEFT JOIN matchup_vote ON matchup_vote.player_id = matchup_option.player_id
            GROUP BY matchup_vote.player_id;

And the return:

matchupID    player_id      name      abr        votes
111          249            Name      SF         0

Expected return:

matchupID    player_id      name      abr        votes
111          249            Name      SF         0
111          331            Name2     JS         0

Thank you!

---

Your group by clause is incorrect. You're grouping by matchup_vote.player_id. I can't see your source data, but this implies that only one player has voted.

Your query should look like:

SELECT
  matchup.matchupID
  , matchup_option.player_id
  , player.name
  , player.abr
  , count(matchup_vote.player_id) votes
FROM
  matchup
  INNER JOIN matchup_option
    ON matchup_option.matchupID = matchup.matchupID
  INNER JOIN player
    ON player.player_id = matchup_option.player_id
  LEFT JOIN matchup_vote
    ON matchup_vote.player_id = matchup_option.player_id
GROUP BY
  matchup.matchupID
  , matchup_option.player_id
  , player.name
  , player.abr