Join Same Column from Same Table Twice-CodePudding

I am new to the SQL world. I would like to replace the Games.home_team_id and Games.away_team_id with the Corresponding entry in the Teams.name column.

First I start by initializing a small table of data:

    CREATE TABLE Games (id,away_team_id INT,away_team_score INT,home_team_id INT, home_team_score INT);
    CREATE TABLE
    INSERT INTO Games (id,away_team_id,away_team_score,home_team_id,home_team_score)
    VALUES
    (1,1,1,2,4),
    (2,1,3,3,2),
    (3,1,1,4,1),
    (4,2,0,3,2),
    (5,2,3,4,1),
    (6,3,5,4,2)
    ;
    INSERT 0 6

Then I create a template of a reference table

    CREATE TABLE Teams (id INT, name VARCHAR(63);
    CREATE TABLE
    INSERT INTO Teams (id, name)
    VALUES
    (1, 'Oogabooga FC'),
    (2, 'FC Milawnchair'),
    (3, 'Ron\'s Footy United'),
    (4, 'Pylon City FC')
    ;
    INSERT 0 4

I would like to have the table displayed as such:

    | id | away_team_name | away_team_score | home_team_name | home_team_score |
    ----- ---------------- ----------------- ---------------- ------------------
    | 1  | Oogabooga FC   | 1               | FC Milawnchair | 4               |
     ...

I managed to get a join query to show the first value from Teams.name in the away_team_name field using this JOIN:

    SELECT
      Games.id,
      Teams.name AS away_team_name,
      Games.away_team_score,
      Teams.name AS home_team_name,
      Games.home_team_score
    FROM Games
      JOIN Teams ON Teams.id = Games.away_team_id
    ;
    | id | away_team_name | away_team_score | home_team_name | home_team_score |
    ----- ---------------- ----------------- ---------------- ------------------
    | 1  | Oogabooga FC   | 1               | Oogabooga FC   | 4               |
    ...

But now I am stuck when I call it twice as a JOIN it shows the error:

     SELECT
       Games.id,
       Teams.name AS away_team_name,
       Games.away_team_score,
       Teams.name AS home_team_name,
       Games.home_team_score
     FROM Games
       JOIN Teams ON Teams.id = Games.away_team_id
       JOIN Teams ON Teams.id = Games.home_team_id
     ;
    ERROR:  table name "teams" specified more than once

How do you reference the same reference the same column of the same table twice for a join?

CodePudding user response：

You need to specify an alias for at least one of the instances of the table; preferably both.

 SELECT
   Games.id,
   Away.name AS away_team_name,
   Games.away_team_score,
   Home.name AS home_team_name,
   Games.home_team_score
 FROM Games
   JOIN Teams AS Away ON Away.id = Games.away_team_id
   JOIN Teams AS Home ON Home.id = Games.home_team_id

Explanation: As you are joining to the same table twice, the DBMS (in your case, PostgreSQL) is unable to identify which of the tables you're referencing to when using its fields; the way to solve this is to assign an alias to the joined tables the same way you assign aliases for your columns. This way you can specify which of the joined instances are you referencing to in your SELECT, JOIN and WHERE statements.