两个表之间的MySQL内部联接 [英] MySQL Inner Join Between Two Tables

问题描述

我正在尝试在 MySQL 中加入两个表.这是我的桌子:

I'm trying to JOIN two tables in MySQL. Here is my 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

我还有另一个表，其中包含一个玩家的ID和该玩家的名字.

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

玩家表:

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

这获得了PlayerOnes的名字，但是我也找不到获得PlayerTwos的名字.我尝试使用两个INNER JOINS，但这没有用.我已经在堆栈上阅读了很多文章，但是还没有遇到任何可行的解决方案.我是MySQL的新手，所以进行后续解释将非常有帮助.

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.

推荐答案

您在正确的轨道上，您确实需要两次加入Players表，如下所示:

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

您可能想念的是使用别名(p1，p2)来区分要加入的Players表的两个副本.

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

这篇关于两个表之间的MySQL内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！