title: Can't figure out why SQL is returning more rows than expected
body: <p>I am building a report based on the results of some SQL.  There is an event that has 3 judges scoring.  I am running into an issue with a specific fight in the night (The event is a fight).  Here is my code:</p>
  
  <pre><code>USE DatabaseName;
  DECLARE @EventID INT = ;
  DECLARE @FightID INT = ;
  ----Judge 1
  SELECT DISTINCT
          JudgeNames.FirstName + ' ' + JudgeNames.LastName AS [Judge Name] ,
          JudgeNames.PersonID ,
          Event.EventID ,
          Fights.FightID ,
          FightScores.RoundNumber ,
          FightScores.Contestant_1_PointsByRound ,
          FightScores.Contestant_1_PointsDeducted ,
          FightScores.Contestant_2_PointsByRound ,
          FightScores.Contestant_2_PointsDeducted
  INTO    #Judge1
  FROM    dbo.tblEvents Event
          INNER JOIN dbo.tblFights Fights ON Event.EventID = Fights.EventID
          INNER JOIN dbo.tblFightJudge FightJudge ON FightJudge.fightid = Fights.FightID
          INNER JOIN dbo.tblPersons JudgeNames ON JudgeNames.PersonID = FightJudge.judge1id
          INNER JOIN dbo.tblEventJudge EJ ON EJ.EventID = Event.EventID
                                             AND EJ.Judge_PersonID = JudgeNames.PersonID
          INNER JOIN dbo.tblFightRoundScore FightScores ON Fights.FightID = FightScores.FightID
                                                           AND FightScores.EventJudgeID = EJ.EventJudgeID
  WHERE   Event.EventID = @EventID
          AND Fights.FightID = @FightID;
  ----Judge 2
  SELECT DISTINCT
          JudgeNames.FirstName + ' ' + JudgeNames.LastName AS [Judge Name] ,
          JudgeNames.PersonID ,
          Event.EventID ,
          Fights.FightID ,
          FightScores.RoundNumber ,
          FightScores.Contestant_1_PointsByRound ,
          FightScores.Contestant_1_PointsDeducted ,
          FightScores.Contestant_2_PointsByRound ,
          FightScores.Contestant_2_PointsDeducted
  INTO    #Judge2
  FROM    dbo.tblEvents Event
          INNER JOIN dbo.tblFights Fights ON Event.EventID = Fights.EventID
          INNER JOIN dbo.tblFightJudge FightJudge ON FightJudge.fightid = Fights.FightID
          INNER JOIN dbo.tblPersons JudgeNames ON JudgeNames.PersonID = FightJudge.judge2id
          INNER JOIN dbo.tblEventJudge EJ ON EJ.EventID = Event.EventID
                                             AND EJ.Judge_PersonID = JudgeNames.PersonID
          INNER JOIN dbo.tblFightRoundScore FightScores ON Fights.FightID = FightScores.FightID
                                                           AND FightScores.EventJudgeID = EJ.EventJudgeID
  WHERE   Event.EventID = @EventID
          AND Fights.FightID = @FightID;
  ----Judge 3
  SELECT DISTINCT
          JudgeNames.FirstName + ' ' + JudgeNames.LastName AS [Judge Name] ,
          JudgeNames.PersonID ,
          Event.EventID ,
          Fights.FightID ,
          FightScores.RoundNumber ,
          FightScores.Contestant_1_PointsByRound ,
          FightScores.Contestant_1_PointsDeducted ,
          FightScores.Contestant_2_PointsByRound ,
          FightScores.Contestant_2_PointsDeducted
  INTO    #Judge3
  FROM    dbo.tblEvents Event
          INNER JOIN dbo.tblFights Fights ON Event.EventID = Fights.EventID
          INNER JOIN dbo.tblFightJudge FightJudge ON FightJudge.fightid = Fights.FightID
          INNER JOIN dbo.tblPersons JudgeNames ON JudgeNames.PersonID = FightJudge.judge3id
          INNER JOIN dbo.tblEventJudge EJ ON EJ.EventID = Event.EventID
                                             AND EJ.Judge_PersonID = JudgeNames.PersonID
          INNER JOIN dbo.tblFightRoundScore FightScores ON Fights.FightID = FightScores.FightID
                                                           AND FightScores.EventJudgeID = EJ.EventJudgeID
  WHERE   Event.EventID = @EventID
          AND Fights.FightID = @FightID;
  ----Fight Info
  SELECT DISTINCT
          Ref.FirstName + ' ' + Ref.LastName AS [Ref Name] ,
          Fights.EventID ,
          Fights.FightID ,
          Fights.Rounds ,
          Fights.ContestantID_1 ,
          Fights.ContestantID_2 ,
          C1.FirstName + ' ' + C1.LastName AS Fighter1 ,
          C2.FirstName + ' ' + C2.LastName AS Fighter2 ,
          Fights.Contestant1CornerColor AS Contestant1CornerColorHEX ,
          Fights.Contestant2CornerColor AS Contestant2CornerColorHEX ,
          Events.EventDate ,
          Fights.Fight_WeightClass ,
          Fights.FightNumber ,
          ( SELECT    COUNT(FightNumber)
            FROM      dbo.tblFights
            WHERE     EventID = Fights.EventID
          ) AS NumOfFights
  INTO    #FightInfo
  FROM    dbo.tblFights Fights
          INNER JOIN dbo.tblPersons Ref ON Fights.Referee_PersonID = Ref.PersonID
          INNER JOIN dbo.tblEvents Events ON Fights.EventID = Events.EventID
          INNER JOIN dbo.tblPersons C1 ON C1.PersonID = Fights.ContestantID_1
          INNER JOIN dbo.tblPersons C2 ON C2.PersonID = Fights.ContestantID_2
  WHERE   Fights.EventID = @EventID
          AND Fights.FightID = @FightID;
  --MainQuery
  SELECT  DISTINCT
          FI.EventID ,
          FI.FightID ,
          FI.FightNumber ,
          FI.NumOfFights ,
          FI.Rounds ,
          FI.EventDate ,
          FI.[Ref Name] ,
          FI.Fight_WeightClass ,
          FI.Contestant1CornerColorHEX ,
          FI.Contestant2CornerColorHEX ,
          FI.Fighter1 ,
          FI.Fighter2 ,
          #Judge1.[Judge Name] AS Judge1 ,
          #Judge2.[Judge Name] AS Judge2 ,
          #Judge3.[Judge Name] AS Judge3 ,
          ISNULL(#Judge1.RoundNumber, 1) AS RoundNumber ,
          --Judge 1
          #Judge1.Contestant_1_PointsByRound AS J1C1Points ,
          #Judge1.Contestant_1_PointsDeducted AS J1C1Deduct ,
          ( #Judge1.Contestant_1_PointsByRound
            - #Judge1.Contestant_1_PointsDeducted ) AS J1C1Total ,
          #Judge1.Contestant_2_PointsByRound AS J1C2Points ,
          #Judge1.Contestant_2_PointsDeducted AS J1C2Deduct ,
          ( #Judge1.Contestant_2_PointsByRound
            - #Judge1.Contestant_2_PointsDeducted ) AS J1C2Total ,
          --Judge 2
          #Judge2.Contestant_1_PointsByRound AS J2C1Points ,
          #Judge2.Contestant_1_PointsDeducted AS J2C1Deduct ,
          ( #Judge2.Contestant_1_PointsByRound
            - #Judge2.Contestant_1_PointsDeducted ) AS J2C1Total ,
          #Judge2.Contestant_2_PointsByRound AS J2C2Points ,
          #Judge2.Contestant_2_PointsDeducted AS J2C2Deduct ,
          ( #Judge2.Contestant_2_PointsByRound
            - #Judge2.Contestant_2_PointsDeducted ) AS J2C2Total ,
            --Judge3
          #Judge3.Contestant_1_PointsByRound AS J3C1Points ,
          #Judge3.Contestant_1_PointsDeducted AS J3C1Deduct ,
          ( #Judge3.Contestant_1_PointsByRound
            - #Judge3.Contestant_1_PointsDeducted ) AS J3C1Total ,
          #Judge3.Contestant_2_PointsByRound AS J3C2Points ,
          #Judge3.Contestant_2_PointsDeducted AS J3C2Deduct ,
          ( #Judge3.Contestant_2_PointsByRound
            - #Judge3.Contestant_2_PointsDeducted ) AS J3C2Total
  INTO    #MQ1
  FROM    #FightInfo FI
          INNER JOIN #Judge1 ON #Judge1.FightID = FI.FightID
          INNER JOIN #Judge2 ON #Judge2.FightID = FI.FightID
          INNER JOIN #Judge3 ON #Judge3.FightID = FI.FightID
  WHERE   FI.FightID = @FightID
          AND FI.EventID = @EventID
  ORDER BY FI.FightNumber;
  
  SELECT  *
  FROM    #Judge1;
  SELECT  *
  FROM    #Judge2;
  SELECT  *
  FROM    #Judge3;
  SELECT  *
  FROM    #FightInfo;
  SELECT  *
  FROM    #MQ1;
  
  DROP TABLE #FightInfo,#Judge1,#Judge2,#Judge3,#MQ1;
  </code></pre>
  
  <p>Each of the 3 judges returns 3 rounds of scores as follows:
  <a href="https://i.stack.imgur.com/1T2Pr.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1T2Pr.jpg" alt="enter image description here"></a></p>
  
  <p>The "FightInfo" Query returns a single row as expected like:
  <a href="https://i.stack.imgur.com/1Fdum.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1Fdum.jpg" alt="enter image description here"></a></p>
  
  <p>The Problem comes when I run the final query.  Judge 3 is returning 2 sets of points for each round.  This is causing me to have 6 rows of data like this:
  <a href="https://i.stack.imgur.com/3SgwK.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3SgwK.jpg" alt="enter image description here"></a></p>
  
  <p>I apologize for the last picture being so small, but as you can see there are 6 rows returned.  It is caused by Judge 3 having different "PointsByRound" numbers though I can't seem to see why or where that is being caused.  Can someone provide some assistance to help me see?</p>