I need to create a database structure for a 1v1 game.
This is what I have designed so far.
Now here is the problem: I want to display all the data of matches on the screen, but instead of the foreign keys (player_1, player_2), I want to show the name of the player which you can find in the players table.
I've tried using MySQL joins to replace for example player_1 with the actual name of the player like this:
SELECT players.name player_1 FROM matches LEFT JOIN players ON player_1 = players.id
This works fine, but now I also need the name of the second player displayed. I've tried to jus add a second left join after the first one but that doesn't work if its the same table.
So does anyone know how to accomplish this and is this design even valid at all?