Connection of 2 tables

1

Hello, and good morning. My issue goes like that:
I have 2 tables "Match" and "Team" where there are 2 foreign keys "home_team_api_id" and "away_team_api_id" on Table Match which connect table Match with table Team to variable team_api_id. Table Team has a column which is named "team_long_name". So, I want to have as my final result a table which contains Home Team Name and Away Team Name, but I cannot find a right solution. Here is my attempt

SELECT Team.team_long_name AS Home_Team, Team.team_long_name AS Away_Team
FROM Match JOIN Team ON Match.home_team_api_id = Team.team_api_id
AND Match.away_team_api_id = Team.team_api_id;

Please, any advice will be extremely helpful. Thank you.

2

can you try this

SELECT t1.team_long_name AS Home_Team,
t2.team_long_name AS Away_Team
FROM [Match] m
inner join Team t1 ON m.home_team_api_id = t1.team_api_id
inner join Team t2 ON m.away_team_api_id = t2.team_api_id;

3

Thank you veryyy much!! Your solution was exactly what I was looking for!!