CODEWITHSUNDEEP
mysqljoin
drent
drent

Reputation: 239

Mysql show count on join even if count is 0

Sorry if my title isn't clear. I’m trying to make a lobby system for my card game. When a user presses join game I want the server to check how much space is available in the game they’ve selected then add them to the team with the least players (currently always 1 player on each team but I’d like to expand it to 2 players per team at some point and possibly more than 2 teams).

The tables I have are:

game_teams

game_id, team_id, score

game_players

user_id, game_id, team_id

With game_teams team_id isn't unique or auto-incremented because it seemed like more overhead to have a unique value for every team in every game when they're mostly just there for player placement. So all games have teams 1-2 or 1-3 depending on team count if that makes sense.

The output I’m hoping for is something like:

game_id | team_id | team_size

   8 |     1 |      1

   8 |     2 |      0

Currently the queries I have are as below but they don’t do what I’m expecting.

This returns all players in team 1 and all players in team 2 ignoring the game id

SELECT games.game_id, COUNT(game_players.user_id) AS team_size, game_teams2.team_id 
FROM games 
JOIN game_teams2 ON games.game_id=game_teams2.game_id 
JOIN game_players ON game_players.team_id=game_teams2.team_id
WHERE games.game_id=1 GROUP BY game_teams2.team_id

This seems to be nearly right but it only returns teams that already have at least 1 player in them where I really need 0 to be returned if the team has no players associated with it.

SELECT game_players.game_id, 
COUNT(game_players.user_id) AS team_size, game_players.team_id 
FROM game_players WHERE game_players.game_id=8 GROUP BY game_players.team_id

I'm not quite sure what else to try to get the desired output.

Any help would be appreciated.

Upvotes: 0

Views: 41

Answers (1)

RP-
RP-

Reputation: 5837

First, you need to create the distinct set of game_id and team_id from game_teams and then left join it with game_players.

Something like

SELECT x.game_id,
       x.team_id,
       count(gp.user_id) AS team_size
FROM
  (SELECT DISTINCT game_id,
                   team_id
   FROM game_teams
   WHERE game_id = 8) x
LEFT JOIN game_players gp ON x.game_id = gp.game_id
AND x.team_id = gp.team_id

You need to take care of nulls for the gp.user_id. I have not tested this, this is just an idea.

Upvotes: 1

Related Questions