mysql - 无法弄清楚如何在加入表格时将客场球员和主场球员分开

Question

我有两个表，一个是schedules 表，另一个是四分卫的stats 表。

我就这样加入了他们..

    SELECT statsSchedules.*
           , awayTeam.fullName     awayQBName
           , awayTeam.passComp     awayPlayerPassComp
           , awayTeam.passAtt      awayPlayerPassAtt
           , awayTeam.passCompPct  awayPlayerPassCompPct
           , awayTeam.passYds        awayPassYds
           , awayTeam.passInts     awayPlayerPassInts
           , awayTeam.passLongest  awayPlayerLongestPass
           , awayTeam.passPts        awayPlayerPassPts
           , awayTeam.passerRating awayPlayerPassRating
           , awayTeam.passSacks    awayQBTimesSacked
           , awayTeam.passTDs        awayQBTds
           , awayTeam.statId       awayStatId

           , homeTeam.fullName     homeQBName
           , homeTeam.passComp     homePlayerPassComp
           , homeTeam.passAtt      homePlayerPassAtt
           , homeTeam.passCompPct  homePlayerPassCompPct
           , homeTeam.passYds        homePassYds
           , homeTeam.passInts     homePlayerPassInts
           , homeTeam.passLongest  homePlayerLongestPass
           , homeTeam.passPts        homePlayerPassPts
           , homeTeam.passerRating homePlayerPassRating
           , homeTeam.passSacks    homeQBTimesSacked
           , homeTeam.passTDs        homeQBTds
           , homeTeam.statId        homeStatId

      FROM statsSchedules
    INNER JOIN statsPassing AS awayTeam
        ON statsSchedules.scheduleId =  awayTeam.scheduleId
    INNER JOIN statsPassing AS homeTeam
        ON statsSchedules.scheduleId =  homeTeam.scheduleId
     WHERE statsSchedules.scheduleId=542769239
     ORDER BY statsSchedules.weekIndex ASC

这样做会导致多次重复统计数据和名称。

我尝试过使用 SELECT DISTINCT statsSchedules.* 但这似乎没有任何作用。

Answer 1

感谢 user3532232 的评论，我能够解决我的问题

我已经很接近了，我需要的是添加

    AND statsSchedules.awayTeamId=awayTeam.teamId
    AND statsSchedules.scheduleId=awayTeam.scheduleId

加上

    AND statsSchedules.homeTeamId=homeTeam.teamId 
    AND statsSchedules.scheduleId=homeTeam.scheduleId

添加这两个 AND 函数后，查询会在 awayTeam 和 homeTeam 列中显示正确的球员，而不会重复。