Getting team names when given two team IDs-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

You should use aliases for the table names and the selected fields as well to be able track and pick columns for the stated tables and advise you use left you get specific rows.

SELECT DISTINCT mr.name as match_results, ht.name as home_team,at.name as
away_team FROM match_results mr LEFT JOIN match m ON mr.match_id = m.id     
LEFT JOIN teams ht m.ht_id = ht.id LEFT JOIN teams at m.at_id = at.id;