two joins on same two tables

Question

I have these two tables. I would like to join the two tables and the real_name from my player table with playerA and playerB in my match table. So I tried this. Unfortunately when I tried this my matches went from 46k records to over a million records so I know this is not working correctly.

SELECT players.player_id, players.real_name, matches.playerA, matches.playerB, matches.scoreA, matches.scoreB
  FROM players
  JOIN matches ON players.player_id = matches.playerA
  JOIN matches m1 ON players.player_id = m1.playerB;

---

+----------+---------+---------+
| match_id | playerA | playerB |
+----------+---------+---------+
|        1 |       4 |      55 |
|        2 |       2 |      41 |
|        3 |      21 |      41 |
|        4 |       3 |       2 |
|        5 |      41 |       2 |
|        6 |      21 |       3 |
|        7 |       1 |       8 |
|        8 |       1 |       8 |
|        9 |       8 |      19 |
|       10 |      19 |      12 |
+----------+---------+---------+

---

+-----------+-----------------+
| player_id | real_name       |
+-----------+-----------------+
|         1 | Dong Nyoung Lee |
|         2 | Hyun Woo Jang   |
|         3 | Seung Hyun Lee  |
|         4 | Soo Ho Park     |
|         5 | Lee Sak Won     |
|         6 | Young Suh Yoon  |
|         7 | Yoon Jong Jung  |
|         8 | Dong Hwan Kim   |
|         9 | Tae Hoon Kwon   |
|        10 | Ilyes Satouri   |
+-----------+-----------------+

---

I added what was asked for.

+-----------+------------------------------------+---------+---------+--------+--------+
| player_id | real_name                          | playerA | playerB | scoreA | scoreB |
+-----------+------------------------------------+---------+---------+--------+--------+
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |
|         4 | Soo Ho Park                        |       4 |      55 |      1 |      3 |

Answer 1

It would be helpful to show the query you were running before, as well as which data you want to display.

What your query is currently doing is, for each player, loading rows from every match in which they were player A, then loading every match in which they were player B. Since SQL doesn't "line up" these two joins, but rather cross-multiplies them (i.e. generates every combination of them), you're likely generating many, many rows for every player id.

If what you're after is a table of match ids with the full names and scores of the participants, that would look more like this:

select m.match_id, pA.full_name, m.scoreA, pB.full_name, m.scoreB from matches m join players pA on pA.player_id = m.playerA join players pB on pB.player_id = m.playerB

Answer 2

If your intent is to get a row/match, I believe what you want is something like:

SELECT p1.player_id, p1.real_name, p2_player_id, p2_player_name, 
     m.scoreA, m.scoreB
FROM matches m
JOIN players p1 ON p1.player_id = m.playerA
JOIN players p2 ON p2.player_id = m.playerB

As it was, you were getting rows for each match player 1 participated in multiplied by every match player 2 participated in for each pairing in table 'match'.