I need to create a database structure for a 1v1 game.

This is what I have designed so far.

enter image description here

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?

  • "that doesn't work" is not a very useful problem statement. I suspect you failed to assign a different correlation name (alias) to the players table when joining it the second time.
    – mustaccio
    Sep 29, 2021 at 13:30

1 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
  • That's called a "self-join".
    – Rick James
    Sep 30, 2021 at 15:35
  • @RickJames ??? self join needed in direct, without intermediate table, joining between two table copies.
    – Akina
    Sep 30, 2021 at 16:17
  • Oops. I was misreading the word "copies". A many-to-many mapping table is involved.
    – Rick James
    Sep 30, 2021 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.