SQL Server query returning the same row twice
I seem to be having problems with the following request. It mostly works, but I have a case where it returns one row out of 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 to change:
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 it did not help. I am still getting a duplicate line.
Can anyone see what's going on?
source to share
What you can do is add the join back to the same table using a group to weed out duplicate rows.
So add this to your connection 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 this makes sense. Basically you make a separate and get the maximum id to exclude one of the strings in the join. You have to remember that even if you want duplicates, they will be eliminated even if they are supposed to be there.
Full change:
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
source to share
Thanks everyone for their input. The general guidelines here were correct: I had two rows in the table mc_workoutDetails
that referenced the same row in the table mc_Activities
.
Although the foreign key was part of a unique primary key, it was a composite key, so that column could contain duplicates if the other column in the key was different.
source to share