CODEWITHSUNDEEP
mysqlsqlinner-join
Jonathan
Jonathan

Reputation: 599

MySQL Inner Join Between Two Tables

I'm trying to JOIN two tables in MySQL. Here is my table:

Games Table:

GameID      Date/Time          PlayerOneID   PlayerTwoID
 13    12/10/2013 10:53:29 PM     1              2    
 14    12/10/2013 10:57:29 PM     1              2
 15    12/10/2013 10:58:29 PM     2              1

I have another table contain the ID of a player and that players name.

Players Table:

1   Dan
2   Jon

I'd like the resulting table to look like the following:

GameID      Date/Time          PlayerOneID   PlayerTwoID
 13    12/10/2013 10:53:29 PM     Dan        Jon      
 14    12/10/2013 10:57:29 PM     Dan        Jon
 15    12/10/2013 10:58:29 PM     Jon        Dan

Here's what I am currently doing:

SELECT Games.GameID, Games.`Date/Time`, Players.Name, PlayerTwoID
FROM Games

INNER JOIN Players
ON PlayerOneID = Players.ID

This gets PlayerOnes name, but I can't find away to get PlayerTwos name as well. I have tried using two INNER JOINS, but this hasn't worked. I've read a lot of posts here on the stack, but I haven't come across any solution that works. I am new to MySQL, so a follow up explanation would be very helpful.

Upvotes: 1

Views: 4972

Answers (2)

yuyonghang
yuyonghang

Reputation: 91

select GameID, p1.name playerOneName,p2.name playerTwoName from
games inner join players p1 on games.playerOneID=p1.id
inner join players p2 on games.playerTwoID=p2.id
order by GameID asc;

the terminal execution result is shown

Upvotes: 0

Eric Petroelje
Eric Petroelje

Reputation: 60498

You were on the right track, you do need to join to the Players table twice, like so:

SELECT Games.GameID, Games.`Date/Time`, p1.Name, p2.Name
FROM Games
INNER JOIN Players p1
ON PlayerOneID = p1.ID
INNER JOIN Players p2
ON PlayerTwoID = p2.ID

What you probably missed was using an alias (p1,p2) to differentiate between the two copies of the Players table you are joining to.

Upvotes: 2

Related Questions