Trouble converting IDs to name values in Postgres-CodePudding

I have a table with the following values:

---- ----------------- ----------------- 
id  | player1_char_id | player2_char_id |
---- ----------------- ----------------- 
1   | 8               | 88              |
---- ----------------- -----------------

player1_char_id and player2_charid both reference IDs from another character table. I'm trying to join the two tables and display the characters' names instead, with the following desired result:

---- -------------- -------------- 
id  | player1_char | player2_char |
---- -------------- -------------- 
1   | Yoshimitsu   | Fahkumram    |
---- -------------- --------------

However, I get no results whatsoever when I try and query the data to achieve the above result with the following code:

SELECT match.id, character.first_name AS player1_char, character.first_name AS player2_char
FROM match
JOIN character
  ON match.player1_char_id = character.id AND match.player2_char_id = character.id;

When I change the AND to OR in the JOIN statement, I get duplicate values in both name columns, i.e.

---- -------------- -------------- 
id  | player1_char | player2_char |
---- -------------- -------------- 
1   | Yoshimitsu   | Yoshimitsu   |
---- -------------- --------------

How can I fix my query to achieve the aforementioned desired result?

CodePudding user response：

SELECT match.id, 
       character_a.first_name AS player1_char,
       character_b.first_name AS player2_char
FROM match
JOIN character as character_a
  ON match.player1_char_id = character_a.id
JOIN character as character_b
  ON match.player2_char_id = character_b.id;