CODEWITHSUNDEEP
mysqljoin
Sababado
Sababado

Reputation: 2532

MySQL Double Join

I have two tables:

teams
----------------
|uid|name |rank|
----------------
|  1  |Team1|  1  |
|  2  |Team2|  2  |
----------------

games
-----------------------------------------------------------------------
|uid|team_one_uid|team_one_score|team_two_uid|team_two_score|game_date|
-----------------------------------------------------------------------
|1|1|70|2|50|2012-12-12|

The teams table has a list of teams and other data like a rank. The games table has a list of games and references each team by it's unique id (uid). What query can I run in order to see a result that contains a row with the following columns:

game_uid, team_one_name, team_one_rank, team_one_score, team_two_name, team_two_rank, team_two_score, game_date

Upvotes: 5

Views: 24197

Answers (2)

juergen d
juergen d

Reputation: 204756

select g.uid as game_uid, 
       t1.name as team_one_name, 
       t1.rank as team_one_rank, 
       team_one_score, 
       t2.name as team_two_name, 
       t2.rank as team_two_rank, 
       team_two_score, 
       game_date
from games g 
inner join teams t1 on t1.uid = team_one_uid
inner join teams t2 on t2.uid = team_two_uid

Upvotes: 12

Falantar014
Falantar014

Reputation: 405

I think this is what you're looking for:

SELECT g.uid AS game_uid, t1.name AS team_one_name, t1.rank AS team_one_rank,
g.team_one_score, t2.name AS team_two_name, t2.rank AS team_two_rank, 
g.team_two_score, g.game_date 
FROM `games` g, `teams` t1, `teams` t2 
WHERE t1.id = g.team_one_uid 
AND t2.id = g.team_two_uid

This can also be done with INNER JOIN but it comes up to the same thing.

Upvotes: 1

Related Questions