SQL Join tables with no unique values

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.

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;