How do I add values from another table into a SQL response?-CodePudding

I have the following tables:

games:

standings:

I am looking to build a table in the following format for each entry in the games table:

visitor_team_standing | home_team_standing | diff

I have been digging into joins to try and learn but still keep running into a problem.

SELECT
    standings.standing visitor_standings,
    games.diff
FROM games
INNER JOIN standings ON
    games.visitor_team_id = standings.team_id;

However, I can't figure out how to also include the standing for home_team (home_team_id = team-_id).

CodePudding user response：

You just join the standings table twice by giving each one a different alias.

SELECT
    visitor.standings visitor_standings,
    home.standings home_standings,
    games.diff
FROM games
INNER JOIN standings as visitor ON
    games.visitor_team_id = visitor.team_id
INNER JOIN standings as home ON
    games.home_team_id = home.team_id;

CodePudding user response：

You can try using this code as well.

SELECT
   visitor.standings visitor_standings,
   home.standings home_standings,
   games.diff
FROM games
INNER JOIN standings as visitor 
ON
  games.visitor_team_id = visitor.team_id
AND 
  games.home_team_id = home.team_id;