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?
>Solution :
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;