Getting team names when given two team IDs

June 28, 2022

I need to get a Select query that lets me display team names instead of IDs.
I store information about teams(ID, Name); match(ID, ht_ID(FK to teams(ID), at_ID(FK to teams(ID)); match_results(ID, Match_ID(FK to match(ID), Results(varchar)) each in seperate table as shown above.

For example, if I do –

select match.ID, match.ht_ID, match.at_ID, match_results.Results from match_results
join match on mattch.ID = match_results.Match_ID

This query is going to output ID, Home team id, Away team ID and the score. How can I write the query so I can see the team names instead of team ids? Thanks!

>Solution :

You need to join twice onto your teams table using a different alias each time

select match.ID, match.ht_ID, ht.Name as ht_Name, match.at_ID, at.Name as at_Name, match_results.Results 
from match_results
join match on mattch.ID = match_results.Match_ID 
join teams ht on match.ht_ID = ht.ID
join teams at on mtach.at_ID = at.ID