2

I have these tables:

  • Games (game_id,game_name, etc)
  • Teams (teams_id,team_name, etc)
  • Players (players_id, player_name, etc.)

junction tables:

  • games_teams (game_id,teams_id)
  • teams_players (teams_id, player_id)

Basically for every game I want to see the number of teams and the number of players.

Game name - Number of teams - number of player

Using inner join I managed to join games with teams but not with players. I believe it involves multiple select statements?

Mureinik
  • 297,002
  • 52
  • 306
  • 350
kattouf
  • 59
  • 6

1 Answers1

2

You can join games and teams with the help of the games_teams junction table. Then, you can join that with teams_players to get the player IDs. Note that for this query you don't even need the players table, since you just needs their number:

SELECT   game_name, 
         COUNT(DISTINCT gt.team_id) AS number_of_teams,
         COUNT(DISTINCT tp.player_id) AS number_of_players
FROM     games g
JOIN     games_teams gt on g.game_id = gt.game_id
JOIN     teams t ON gt.team_id = t.team_id
JOIN     teams_players tp ON t.team_id = tp.team_id
GROUP BY game_name
Mureinik
  • 297,002
  • 52
  • 306
  • 350