mysql - 内部连接问题

Question

我尝试使用内部联接将 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"

我的问题是，当我运行这个查询时，击球手得到的结果比他们应该得到的多。例如，在第一局中，Batter jay Bruce(atbats 表中的数字 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 添加了类似的 AND，因为尽管 batters.ID 的值足够大，看起来似乎这确实是一个独特的领域，为了保持一致性将其包括在内是有意义的。)