I am trying to create a table for a game that shows some player and team stats so I can compare them. The two tables look like this:
year player team patch game result kills assists deaths 2018 Impact Team Liquid 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440062?gameHash=a3b08c115923f00d 1 3 6 0 2018 Hauntzer Team SoloMid 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440062?gameHash=a3b08c115923f00d 0 0 0 3 2018 Ssumday 100 Thieves 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440076?gameHash=c426d3d50426edb3 1 1 5 3 2018 zig OpTic Gaming 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440076?gameHash=c426d3d50426edb3 0 1 4 3 2018 Lourlo Golden Guardians 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440084?gameHash=f0f86e52b6e472e9 0 1 1 1 2018 Huni Echo Fox 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440095?gameHash=fd3b9331ff5312e3 1 4 6 0 2018 Flame FlyQuest 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440095?gameHash=fd3b9331ff5312e3 0 0 1 2 2018 ZionSpartan Counter Logic Gaming 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440106?gameHash=d6441d4ec8f87534 0 2 4 3 2018 Licorice Cloud9 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440106?gameHash=d6441d4ec8f87534 1 2 5 3
The thing to note here is that each player has a team, and there are two rows for each distinct game id (since there is one player on each team for each game).
The other table I have is for each team and their aggregate results, so in the above table, the kills/assists/deaths belong to the individual player, while below is the aggregate of the 5 players on each team.
year team player patch game result teamkills assists teamdeaths 2018 Team Liquid 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440062?gameHash=a3b08c115923f00d 1 11 36 1 2018 Team SoloMid 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440062?gameHash=a3b08c115923f00d 0 1 2 11 2018 100 Thieves 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440076?gameHash=c426d3d50426edb3 1 9 27 7 2018 OpTic Gaming 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440076?gameHash=c426d3d50426edb3 0 7 17 9 2018 Golden Guardians 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440084?gameHash=f0f86e52b6e472e9 0 2 7 7 2018 Echo Fox 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440095?gameHash=fd3b9331ff5312e3 1 15 40 2 2018 FlyQuest 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440095?gameHash=fd3b9331ff5312e3 0 2 7 15 2018 Counter Logic Gaming 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440106?gameHash=d6441d4ec8f87534 0 8 19 12 2018 Cloud9 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440106?gameHash=d6441d4ec8f87534 1 12 34 8 2018 OpTic Gaming 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440127?gameHash=361bcc2e848641d2 0 6 15 13 2018 Team Liquid 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440127?gameHash=361bcc2e848641d2 1 13 45 6 2018 FlyQuest 8.01 https://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440132?gameHash=23453515b5a50136 1 14 42 9 2018 Team SoloMid 8.01 https://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440132?gameHash=23453515b5a50136 0 9 23 14
I’m trying to join these columns so I can get a percentage for each player in each game. The problem is that when I try to join on the games column, it creates 4 rows. Player A on team A, Player A on team B, player B on team B, and Player B on team A.
The syntax I’m using is this:
create table toplaner_kill_participation
select
tl.player as TopLane_player,
tl.kills as TopLaner_kills,
tl.assists as TopLaner_assists,
tl.deaths as TopLaner_deaths,
t.*
from
LCS_top_teams_kda t
join LCS_top_top_kda tl
on tl.games = t.games
order by t.patch;
The output:
TopLane_Player TopLaner_kills TopLAner_assists TopLaner_deaths year team player patch games result teamkills assists teamdeaths Impact 3 6 0 2018 Team Liquid 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440062?gameHash=a3b08c115923f00d 1 11 36 1 Impact 3 6 0 2018 Team SoloMid 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440062?gameHash=a3b08c115923f00d 0 1 2 11 Hauntzer 0 0 3 2018 Team Liquid 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440062?gameHash=a3b08c115923f00d 1 11 36 1 Hauntzer 0 0 3 2018 Team SoloMid 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440062?gameHash=a3b08c115923f00d 0 1 2 11 Ssumday 1 5 3 2018 100 Thieves 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440076?gameHash=c426d3d50426edb3 1 9 27 7 Ssumday 1 5 3 2018 OpTic Gaming 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440076?gameHash=c426d3d50426edb3 0 7 17 9 zig 1 4 3 2018 100 Thieves 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440076?gameHash=c426d3d50426edb3 1 9 27 7 zig 1 4 3 2018 OpTic Gaming 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440076?gameHash=c426d3d50426edb3 0 7 17 9 Lourlo 1 1 1 2018 Golden Guardians 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440084?gameHash=f0f86e52b6e472e9 0 2 7 7 Huni 4 6 0 2018 Echo Fox 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440095?gameHash=fd3b9331ff5312e3 1 15 40 2 Huni 4 6 0 2018 FlyQuest 8.01 http://matchhistory.na.leagueoflegends.com/en/#match-details/TRLH1/1002440095?gameHash=fd3b9331ff5312e3 0 2 7 15
What you can see here in the first 4 rows is what I described above. I understand that this is a result of the way the join works, but I’m just not sure how to prevent it from doing that. There isn’t really a primary key or other unique single column value I can use to join the tables.
I can create a table that shows each player and what team they were on for each patch using select distinct, and I’m wondering if I can do a second join on that table which would eliminate the erroneous entries, but I figure if there was a way to do that in the first join then that would be more efficient and therefore good to know.
Thanks.
Advertisement
Answer
No special need for primary/foreign keys here. There is missing second condition in the join.
select tl.player as TopLane_player, tl.kills as TopLaner_kills, tl.assists as TopLaner_assists, tl.deaths as TopLaner_deaths, t.* from LCS_top_teams_kda t join LCS_top_top_kda tl on tl.game = t.game and tl.team = t.team order by t.patch;