Reputation: 117
What I'm trying to do is to switch up an id with the corresponding name from another table.
Teams:
1 - team_01
2 - team_02
Games:
team_a team_b score_a score_b
1 2 30 40
What I want to get is:
Games:
team_a team_b score_a score_b
team_01 team_02 30 40
I try:
SELECT
games.id
, games.score_team_a
, games.score_team_b
, games.time
, games.category
, games.team_a
, games.team_b
FROM games
LEFT JOIN teams t1 ON t1.id = games.team_a
LEFT JOIN teams t2 ON t2.id = games.team_b
Upvotes: 0
Views: 47
Reputation: 164064
You don't need a left join, I don't see why the team ids in the games
table would not match an id inside the teams
table.
Also if you need only 4 columns, why do you select all the other columns?
SELECT
t1.name team_a,
t2.name team_b,
g.score_a,
g.score_b
FROM games g
INNER JOIN teams t1 ON t1.id = g.team_a
INNER JOIN teams t2 ON t2.id = g.team_b
Upvotes: 0
Reputation: 48177
SELECT
games.id
, games.score_team_a
, games.score_team_b
, games.time
, games.category
, t1.<team_name> as team_a -- reference the join tables
, t2.<team_name> as team_b
FROM games
LEFT JOIN teams t1 ON t1.id = games.team_a
LEFT JOIN teams t2 ON t2.id = games.team_b
Upvotes: 1