sql - How to distinguish these two ID requests when joining tables?

Question

SQL noob here. I have a database of soccer matches that I am trying to learn/practice SQL with.

In one table (called "Match") there is the match_api_id, date, home_team_api_id, away_team_api_id, home_team_goal, away_team_goal. In another table (called "Team") there is the team_api_id, team_long_name.

Right now I am trying to do a query to show the ID of the match, the date, the home team's name, and the away team's name.

SELECT M.match_api_id, M.date, T.team_long_name, T.team_long_name
FROM Match M
JOIN Team T ON (M.home_team_api_id = T.team_api_id)
JOIN Team T ON (M.away_team_api_id = T.team_api_id)
LIMIT 10

This code worked when I only used one join (the first one) to show the home team's name. However, when I add the second join it gives me the ambiguous column name error. How do I alter this code so that I can also display the away team's name?

question from:https://stackoverflow.com/questions/66049584/how-to-distinguish-these-two-id-requests-when-joining-tables

Answer 1

YOu need different table aliases. Otherwise T is ambiguous:

SELECT M.match_api_id, M.date, TH.team_long_name, TA.team_long_name
FROM Match M JOIN 
     Team TH
     ON M.home_team_api_id = TH.team_api_id JOIN
     Team TA
     ON M.away_team_api_id = TA.team_api_id;
LIMIT 10