CODEWITHSUNDEEP
mysql
worker
worker

Reputation: 93

How can I get the name of the team instead of its ID

I have 2 tables

teams

matches (bridge table)

How could I manage to get TeamName instead of TeamID in the final table?
Already tried using, inner and outer joins etc.

Here is my query:

SELECT CONCAT(m.MatchID, m.TeamID_1, m.TeamID_2) AS MatchID, m.TeamID_1 AS 'Team 1', m.TeamID_2 AS 'Team 2', m.MatchWinner AS 'Winner'
    FROM teams t
    JOIN matches m
    ON t.TeamID IN (m.TeamID_1, m.TeamID_2)
    WHERE t.TeamName = 'Fnatic';

issue

The final table should look like

MatchID - Team 1 - Team 2 - Winner
18582 - Fnatic - Astralis - Fnatic

Upvotes: 0

Views: 136

Answers (1)

forpas
forpas

Reputation: 164089

You must join matches with 2 copies of teams:

select m.matchid, t1.teamname team1, t2.teamname team2, m.Winner
from matches m 
inner join teams t1 on t1.teamid = m.teamid_1 
inner join teams t2 on t2.teamid = m.teamid_2
where 'Fnatic' in (t1.teamname, t2.teamname)

Upvotes: 1

Related Questions