内部连接问题

Question

Im试图使用内部连接将3个表连接在一起，但是结果显示的记录比应该记录的要多。我的数据表设置如下：

Table:gameday.atbats

   GameName                     Inning num  b   s   o   Batter   Pitcher      Result
-----------------------------------------------------------------------------------------
    gid_2008_09_24_cinmlb_houmlb_1  1   1   2   3   1   457803  150116  Jay Bruce strikes out swinging.  
    gid_2008_09_24_cinmlb_houmlb_1  1   2   1   0   2   433898  150116  Jeff Keppinger lines out to right fielder Hunter Pence.  
    gid_2008_09_24_cinmlb_houmlb_1  1   3   3   1   2   458015  150116  Joey Votto singles on a line drive to right fielder Hunter Pence.  
    gid_2008_09_24_cinmlb_houmlb_1  1   4   2   3   3   429665  150116  Edwin Encarnacion called out on strikes.  
    gid_2008_09_24_cinmlb_houmlb_1  1   5   1   2   0   430565  459371  Kazuo Matsui singles on a line drive to right fielder Jay Bruce.  
-----------------------------------------------------------------------------------------

Table: Gameday.pitches
 GameName                   GameAtBatID      Result
------------------------------------------------------
gid_2008_09_24_cinmlb_houmlb_1  1       Called Strike
gid_2008_09_24_cinmlb_houmlb_1  1       Ball
gid_2008_09_24_cinmlb_houmlb_1  1       Swinging Strike
gid_2008_09_24_cinmlb_houmlb_1  1       Ball
gid_2008_09_24_cinmlb_houmlb_1  1       Foul
gid_2008_09_24_cinmlb_houmlb_1  1       Foul
gid_2008_09_24_cinmlb_houmlb_1  1       Swinging Strike
gid_2008_09_24_cinmlb_houmlb_1  2       Ball
gid_2008_09_24_cinmlb_houmlb_1  2       In play, out(s)
gid_2008_09_24_cinmlb_houmlb_1  3       Called Strike
gid_2008_09_24_cinmlb_houmlb_1  3       Ball
--------------------------------------------------------

Table:batters
   GameName                     id         name_display_first_last
----------------------------------------------------------------------------------
gid_2008_09_24_cinmlb_houmlb_1  407783      Geoff Geary
gid_2008_09_24_cinmlb_houmlb_1  209315      David Newhan
gid_2008_09_24_cinmlb_houmlb_1  115629      LaTroy Hawkins
gid_2008_09_24_cinmlb_houmlb_1  113889      Darin Erstad
gid_2008_09_24_cinmlb_houmlb_1  457803      Jay Bruce
gid_2008_09_24_cinmlb_houmlb_1  433898      Jeff Keppinger
gid_2008_09_24_cinmlb_houmlb_1  458015      Joey Votto
gid_2008_09_24_cinmlb_houmlb_1  429665      Edwin Encarnacion
---------------------------------------------------------------------------

我正在运行一个看似相当标准的内部连接集合，将每个不同的表连接在一起，得到一个输出，它显示了每个击球手在整个游戏中所做的每一个音高。我的代码如下：

SELECT 


    gameday.atbats.inning,
    gameday.batters.name_display_first_last,
    gameday.pitches.Result
FROM
 gameday.atbats
        Inner join 
     gameday.pitches on gameday.atbats.num = gameday.pitches.gameAtBatID
        inner join
    gameday.batters on gameday.atbats.batter = gameday.batters.ID

    where gameday.atbats.gamename = "gid_2008_09_24_cinmlb_houmlb_1"

我的问题是，当我运行这个查询时，击球手的结果比他们应该得到的要多。例如，在第一局击球手杰伊·布鲁斯(atbat表中的num 1)第一局应该有7个投手投给他，但是当我进行查询时，他会被扔给他10个投手。我做错了什么才能得到这些结果。而且，我知道这些字段名的命名非常可怕，但它们是由其他人命名的，而且我还没有机会更改它们。

Answer 1

我打赌，atbats.num和pitches.GameAtBatID并不意味着在全球范围内唯一地识别一个at-bat，而是它们只在给定的游戏中唯一地识别一个at-bat。因此，除了将atbats.GameName限制在所需的游戏之外，还需要指定pitches.GameName = atbats.GameName

SELECT gameday.atbats.inning,
       gameday.batters.name_display_first_last,
       gameday.pitches.Result
  FROM gameday.atbats
  JOIN gameday.pitches
    ON gameday.atbats.GameName = gameday.pitches.GameName
   AND gameday.atbats.num = gameday.pitches.GameAtBatID
  JOIN batters
    ON gameday.atbats.GameName = gameday.batters.GameName
   AND gameday.atbats.batter = gameday.batters.ID
 WHERE gameday.atbats.gamename = 'gid_2008_09_24_cinmlb_houmlb_1'

(注意:我还包括了用于batters的类似的batters，因为尽管batters.ID的值足够大，看起来确实是一个独特的字段，但是为了一致性而包含它是有意义的。)

Answer 2

这是正确的，因为SQL从上到下都是工作的，所以当您连接前两个表时，您将拥有

Inner join 
     gameday.pitches on gameday.atbats.num = gameday.pitches.gameAtBatID

你会得到这些结果

GameName                   GameAtBatID      Result         Batter    
--------------------------------------------------------------------------
gid_2008_09_24_cinmlb_houmlb_1  1       Called Strike      457803 
gid_2008_09_24_cinmlb_houmlb_1  1       Ball               457803 
gid_2008_09_24_cinmlb_houmlb_1  1       Swinging Strike    457803 
gid_2008_09_24_cinmlb_houmlb_1  1       Ball               457803 
gid_2008_09_24_cinmlb_houmlb_1  1       Foul               457803 
gid_2008_09_24_cinmlb_houmlb_1  1       Foul               457803 
gid_2008_09_24_cinmlb_houmlb_1  1       Swinging Strike    457803 
gid_2008_09_24_cinmlb_houmlb_1  2       Ball               433898
gid_2008_09_24_cinmlb_houmlb_1  2       In play, out(s)    433898
gid_2008_09_24_cinmlb_houmlb_1  3       Called Strike      458015 
gid_2008_09_24_cinmlb_houmlb_1  3       Ball               458015 

然后，当您添加新的联接行时，

inner join
    gameday.batters on gameday.atbats.batter = gameday.batters.ID

您将从三个表中得到这些结果

name_display_first_last   GameAtBatID      Result          Batter    
    --------------------------------------------------------------------------
    Jay Bruce                1       Called Strike      457803 
    Jay Bruce                1       Ball               457803 
    Jay Bruce                1       Swinging Strike    457803 
    Jay Bruce                1       Ball               457803 
    Jay Bruce                1       Foul               457803 
    Jay Bruce                1       Foul               457803 
    Jay Bruce                1       Swinging Strike    457803 
    Jeff Keppinger           2       Ball               433898
    Jeff Keppinger           2       In play, out(s)    433898
    David Newhan             3       Called Strike      458015 
    David Newhan             3       Ball               458015