A table that has two foreign keys to the same column in another table?

Question

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?

Answer 1

You must join 2 copies of players table.

SELECT m.id, p1.name player_1, p2.name player_2 
FROM matches m
JOIN players p1 ON m.player_1 = p1.id
JOIN players p2 ON m.player_2 = p2.id