I need help in SQL , regarding this database https://www.kaggle.com/hugomathien/soccer. I need an SQL statement that gives a table of the home team name and the away team name for a random game ( I chose Match.match_api_id = 492476) If i run this :

SELECT Team.team_long_name , Match.home_team_api_id
FROM Team JOIN Match
ON Match.away_team_api_id = Team.team_api_id 
WHERE Match.match_api_id = 492476;  

I get the away team name , but not the home team name (instead I get the value of Match.home_team_api_id , as expected). If I run :

SELECT Team.team_long_name , Match.away_team_api_id
FROM Team JOIN Match
ON Match.home_team_api_id = Team.team_api_id 
WHERE Match.match_api_id = 492476;  

I get the home team name , but not the away team name (instead I get the value of Match.away_team_api_id , as expected). The problem is that both of the foreign keys Match.home_team_api_id and Match.away_team_api_id corespond to : Team.team_api_id , so when I get one I "lose" the other.

Is there an SQL statement to get both the home team name and the away team name of random match in the same table?

CodePudding user response：

You can join the Team table twice, once for the away team and once for the home team. The aliases for the tables help track which is which. Something like:

SELECT 
 hometeam.team_long_name homename,
 Match.home_team_api_id, 
 awayteam.team_long_name awayname, 
 Match.away_team_api_id
FROM Match INNER JOIN Team hometeam
  ON Match.home_team_api_id = hometeam.team_api_id
 INNER JOIN Team awayteam
  ON Match.away_team_api_id = awayteam.team_api_id 
WHERE Match.match_api_id = 492476

CodePudding user response：

First, if the selection criteria is solely about the Match table, it should be the first table you mention.

Second, you're allowed to use JOIN (or INNER JOIN as it's more often written) more than once.

SELECT T1.team_long_name as Away_team_long_name,
       T2.team_long_name as Home_team_long_name
FROM Match
   INNER JOIN Team as T1
      ON Match.away_team_api_id = T1.team_api_id 
   INNER JOIN Team as T2
      ON Match.home_team_api_id = T2.team_api_id
WHERE Match.match_api_id = 492476;  

Rephrasing this as a human procedure:

1. Use the match_api_id to look up the Match row in the Match table.
2. Take the away_team_api_id from the Match row and use it to look up a Team in the Team table. Report the team_long_name of that Team as the Away team long name.
3. Take the home_team_api_id from the Match row and use it to look up a Team in the Team table. Report the team_long_name of that Team as the Home team long name.