CODEWITHSUNDEEP
mysql
chrise
chrise

Reputation: 4253

mysql replace ids with names from other table in query

Hi I have a table like

matches

match_id, home_team_id, away_team_id, home_team_player_1_id, home_team_player_2_id, ....

and I have tables 

teams

team_id, team_name


players

player_id, player_name

Now I am trying to get the query the matches table with the actual names instead of ids.

query_result

match_id, home_team_name, away_team_name, home_team_player_1_name, ...

Is there a neat way to 'replace' ids with the lookup values from the tables? The only way to do this that I can come up with is to join the teams table for each team and then the player name table for each player. Is there a better way to do this than writing 50 joins?

I dont want to alter the actual match table, just get the names in the query.

Upvotes: 0

Views: 280

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You need to join the tables 2 times each with a different alias something as

select
m.match_id,
t1.team_name as home_team_name,
t2.team_name as away_team_name,
p1.player_name as home_team_player_1_name
p2.player_name as home_team_player_2_name
from matches m
left join teams t1 on t1.team_id = m.home_team_id
left join teams t2 on t2.team_id = m.away_team_id
left join players p1 on p1.player_id = m.home_team_player_1_id
left join players p2 on p2.player_id = m.home_team_player_2_id

Upvotes: 1

Related Questions