Joining player and game tables to get player points

I have the following SQL tables and I’m basically trying to pull a table of every game that Ralph played in for 2018, and the amount of points scored.

Ralph has a unique_id, but may play on multiple teams, or in different positions. Each year that he plays has a new record entered into the player info table for each of those teams and/or positions.

The games data table’s player ID may use both of Ralph’s player info records, so for instance, records 1 and 2 of game data are both for Ralph, and his actual total points scored is 18 (12 + 6). I don’t need those points to be added together, as that can be done easier in PHP, but I do need both records pulled.

------------------------------
          Player Info as pi
------------------------------
id | unique_id | year | name | team | pos
1     5000       2018   Ralph   5       F
2     5000       2018   Ralph   5       C
3     5600       2018   Bill    5       G
4     5000       2017   Ralph   4       F
5     2688       2016   Mike    6       G


------------------------------
          Game Info as gi
------------------------------
id  | team 1 | team 2
1        5       6
2        6       5
3        8       3
4        6       2


------------------------------
          Game Data as gd
------------------------------
id  | game_info_id | player_id | Points
1         1               1        12   
2         1               2        6    
3         2               1        4    
4         4               5        6

The table should show pi.id, pi.unique_id, gi.id, gd.* WHERE gd.player_id = Any of Ralph’s pi.id’s AND pi.year=2018

Any help here is appreciated, this seems a bit out of my wheelhouse.

Answer

Join the tables like this:

select
  pi.id, pi.unique_id, gi.id, gd.*
from playerinfo pi
inner join gameinfo gi on pi.team in (gi.team1, gi.team2)
inner join gamedata gd on gd.game_info_id = gi.id and gd.player_id = pi.id
where pi.name = 'Ralph' and pi.year = 2018

Advertisement

Answer