CODEWITHSUNDEEP
mysql
Matias Diez
Matias Diez

Reputation: 1297

How can I show values for two diferent columns that point to the same table in mySQL?

I have a "Goal" table for a football league. My table contains the "Goal Player" and the "Goal Assist", like this: 

Select idgoal, goal_player, goal_assist from myDB.goal LIMIT 3;

+--------+-------------+-------------+
| idgoal | goal_player | goal_assist |
+--------+-------------+-------------+
|      1 |          14 |          15 |
|      2 |          16 |           0 |
|      3 |          17 |          18 |
+--------+-------------+-------------+

The IDs under goal_player and goal_assist, both refer to a specific player in my "players" table. In rder to present my results on a redable way I have to replace the ids for the actual names so I'm doing: 

SELECT idgoal, player.player_nickname as goal_player, goal_assist 
   FROM myDB.goal 
   JOIN player 
   ON goal_player = player.idplayer LIMIT 3;

+--------+-------------+-------------+
| idgoal | goal_player | goal_assist |
+--------+-------------+-------------+
|      1 | Maxi        |          15 |
|      2 | El Loco     |           0 |
|      3 | El Bebe     |          18 |
+--------+-------------+-------------+

I can't do this for both goal_player and goal_assist because it would imply the same player does both the goal and the assist so what I did was to create a view with the first Join, and then create a second view using that first view, replacing the "goal_assist" .

Worked like a charm, until I had to do the same with the roster that had a match_id and 22 players instead of these 2.

So, how can I replace the IDs with the values from the original table when both columns are pointing to the same table?

Upvotes: 0

Views: 28

Answers (2)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

You might try this:

SELECT 
    idgoal, 
    (SELECT player_nickname FROM player WHERE player.idplayer = goal_player) as `goal_player_name`,
    (SELECT player_nickname FROM player WHERE player.idplayer = goal_assist) as `goal_assist_name`,
FROM myDB.goal

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You are looking for two joins . . . along with aliases:

SELECT g.idgoal, p.player_nickname as goal_player,
       pa.player_nickname as assist_player 
FROM myDB.goal g JOIN
     player p
     ON g.goal_player = p.idplayer LET JOIN
     player pa
     ON g.goal_assist = pa.idplayer
LIMIT 3;

Notes:

  • All tables are given aliases.
  • All column references are "qualified", meaning they refer to the table where the column is.
  • You need two joins.
  • The second join is a LEFT JOIN, to handle the case where a goal has no "assist" player.

Upvotes: 1

Related Questions