CODEWITHSUNDEEP
mysqlleft-join
Nihat
Nihat

Reputation: 65

mysql left join not returning 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!

Upvotes: 1

Views: 46

Answers (2)

gview
gview

Reputation: 15361

You are doing a GROUP BY matchup_vote.player_id;

You will get one row per player_id.

Upvotes: 1

Jacobm001
Jacobm001

Reputation: 4539

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

Upvotes: 2

Related Questions