Reputation: 2668

Can't figure out why SQL is returning more rows than expected

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:

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;

Each of the 3 judges returns 3 rounds of scores as follows:

The "FightInfo" Query returns a single row as expected like:

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:

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?

Upvotes: 0

Answers (4)

SQLChao

Reputation: 7847

On your joins for the #MQ1 table add RoundNumber.

This issue is if you join just on fightid if you get 27 rows. Because the fightids match all rows.

    INNER JOIN #Judge1 ON #Judge1.FightID = FI.FightID 
    INNER JOIN #Judge2 ON #Judge2.FightID = FI.FightID AND #Judge2.RoundNumber = #Judge1.RoundNumber
    INNER JOIN #Judge3 ON #Judge3.FightID = FI.FightID AND #Judge3.RoundNumber = #Judge1.RoundNumber

Upvotes: 1

eftpotrm

Reputation: 2281

I can't see an obvious problem on the screen, so this is what I'd do to debug.

Alias all the table names. Bulky queries are harder to read.
Put all three judges into a single judge info table with a parameter to specify the judge ID / number / whatever you need for this purpose. You'll have less repeated logic which will make this easier to read and maintain, and can just join to the same table 3 times with a different parameter in the join criteria to get your final result.
Once the three judges are in one table I suspect all your score calcs can be done in a CTE / temp table. Again, less repeated logic.

By now I'd be hopeful that the cleanup wouldn've found and fixed the issue :-) If it's still there -

Take off the DISTINCT on the final query. You shouldn't need it for that query from what I can see, and it can mask join issues.
Comment out everything relating to judges 1 and 2, just to make sure it's definitely not a problem they're creating somehow.
Comment almost everything out until you've got the absolute minimum result. What's the most basic query you can run that gives you the 6 problem rows rather than the 3 you want? That should show where the problem is.

Upvotes: 0