SQL Server Query Returning Same Row Twice

Question

I seem to be having trouble with the following query. It basically works, but I have a case where it is returning one row from mc_WorkoutDetails twice!

Here's the original query:

ALTER PROCEDURE [dbo].[mc_Workouts_GetActivities]
    @WorkoutID                  bigint
AS
BEGIN
    SET NOCOUNT ON

    SELECT d.ID, a.Description,
            CASE WHEN Reps = 0 THEN NULL ELSE Reps END AS Reps,
            CASE WHEN Sets = 0 THEN NULL ELSE Sets END AS Sets,
            CASE WHEN Minutes = 0 THEN NULL ELSE Minutes END AS Minutes,
            d.Comments, c.Name AS Category, a.CategoryID,
            (CASE WHEN v.ActivityID IS NULL THEN 0 ELSE 1 END) AS HasVideo,
            a.ID AS ActivityID
    FROM mc_WorkoutDetails d
            INNER JOIN mc_Activities a ON d.ActivityID = a.ID
            INNER JOIN mc_Activities_Categories c ON a.CategoryID = c.ID
            LEFT OUTER JOIN mc_TrainerVideos v ON a.ID = v.ActivityID
    WHERE (d.WorkoutID = @WorkoutID)
    ORDER BY SortOrder, a.Description

    RETURN @@ERROR
END

Then I tried changing:

INNER JOIN mc_Activities a ON d.ActivityID = a.ID
INNER JOIN mc_Activities_Categories c ON a.CategoryID = c.ID

To:

LEFT OUTER JOIN mc_Activities a ON d.ActivityID = a.ID
LEFT OUTER JOIN mc_Activities_Categories c ON a.CategoryID = c.ID

But that didn't seem to help. I still get the duplicate row.

Can anyone see what's happening?

Answer 1

What you can do is add a join back to the same table using a group to weed out the duplicate rows.

So add this to your join after FROM mc_WorkoutDetails d:

inner join (select [columns you want to select], max(id) id
            from mc_WorkoutDetails
            group by [columns you want to select] ) q on q.id = d.id

Let me know if that makes sense. Basically you are doing a distinct and getting the max id so you eliminate one of the rows in the join. You have to remember that even if you want there to be duplicates, they will be eliminated even if they are suppose to be there.

The full alter would be:

ALTER PROCEDURE [dbo].[mc_Workouts_GetActivities]
@WorkoutID                  bigint
AS
BEGIN
SET NOCOUNT ON

SELECT d.ID, a.Description,
        CASE WHEN Reps = 0 THEN NULL ELSE Reps END AS Reps,
        CASE WHEN Sets = 0 THEN NULL ELSE Sets END AS Sets,
        CASE WHEN Minutes = 0 THEN NULL ELSE Minutes END AS Minutes,
        d.Comments, c.Name AS Category, a.CategoryID,
        (CASE WHEN v.ActivityID IS NULL THEN 0 ELSE 1 END) AS HasVideo,
        a.ID AS ActivityID
FROM mc_WorkoutDetails d
inner join (select Reps, Sets, Comments, Minutes, max(id) id
            from mc_WorkoutDetails
            group by Reps, Sets, Comments, Minutes ) q on q.id = d.id
        INNER JOIN mc_Activities a ON d.ActivityID = a.ID
        INNER JOIN mc_Activities_Categories c ON a.CategoryID = c.ID
        LEFT OUTER JOIN mc_TrainerVideos v ON a.ID = v.ActivityID
WHERE (d.WorkoutID = @WorkoutID)
ORDER BY SortOrder, a.Description

RETURN @@ERROR
END