Tai Alt
Tai Alt

Reputation: 117

How to join the same table twice

What I'm trying to do is to switch up an id with the corresponding name from another table.

Teams:

 1 - team_01 
 2 - team_02

Games:

 team_a     team_b   score_a   score_b
   1           2        30        40

What I want to get is:
Games:

 team_a     team_b   score_a   score_b
 team_01    team_02    30        40

I try:

    SELECT 
      games.id
    , games.score_team_a
    , games.score_team_b
    , games.time
    , games.category
    , games.team_a
    , games.team_b

    FROM games 

    LEFT JOIN teams t1 ON t1.id = games.team_a
    LEFT JOIN teams t2 ON t2.id = games.team_b

Upvotes: 0

Views: 47

Answers (2)

forpas
forpas

Reputation: 164064

You don't need a left join, I don't see why the team ids in the games table would not match an id inside the teams table.
Also if you need only 4 columns, why do you select all the other columns?

   SELECT 
     t1.name team_a,
     t2.name team_b,
     g.score_a,
     g.score_b
   FROM games g
   INNER JOIN teams t1 ON t1.id = g.team_a
   INNER JOIN teams t2 ON t2.id = g.team_b

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

SELECT 
  games.id
, games.score_team_a
, games.score_team_b
, games.time
, games.category
, t1.<team_name> as team_a  -- reference the join tables
, t2.<team_name> as team_b

FROM games 

LEFT JOIN teams t1 ON t1.id = games.team_a
LEFT JOIN teams t2 ON t2.id = games.team_b

Upvotes: 1

Related Questions