Duplicate Returns in Application

Question

Duplicate Returns in Application

Mark-459099

SSC Eights!

Points: 911
More actions
July 14, 2008 at 6:58 pm

#189526

Today during testing, something totally weird began to happen. I wondered if anyone experienced this before. My stored procedure is returning duplicate records even though there is only one record in the database:
Here is the stored procedure:
ALTER PROCEDURE [dbo].[sp_GetReport]
@classid int,
@period int
AS
BEGIN
SET NOCOUNT ON;
SELECT Roster.Student, Classes.ClassName, Roster.ClassID, Scores.Score, Assignments.AssignmentName, Assignments.Category, Assignments.Period, Categories.CategoryName, Final.Final FROM Roster JOIN Scores ON Roster.sID=Scores.sID JOIN Assignments ON Scores.AssignmentID=Assignments.AssignmentID JOIN Categories ON Categories.Category=Assignments.Category JOIN Classes ON Classes.ClassID=Roster.ClassID JOIN Final ON Final.sID=Roster.sID WHERE Roster.ClassID=@classid AND Assignments.Period=@period ORDER BY Roster.Student ASC
END
Seems okay to me. I look in the database and see for example there is only one assignment, a score for the assignment, one classid and period, etc.
The return should be for example:
Assignment1 50
But instead it returns:
Assignment1 50
Assignment1 50
Now for every one assignment with score in the database, I get two returns. Another example:
Assignment1 50
Assignment2 60
But instead it returns:
Assignment1 50
Assignment1 50
Assignment2 60
Assignment2 60
In some instances, it duplicated the record four times!
I found this really weird. It even shows up like that in Crystal Reports! What the heck is going on? Does anyone know or have experienced this. Please help. I'd like to hear from anyone.
Thanks alot.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 1

Try using Distinct in your query.

JOIN acts like inner join. One of your our tables might be having duplicate value in the column used in the join condition. that is why it is showing the duplicate records.

I think Distinct will do the job.

Atif Sheikh

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

To check in which table(s) the duplicate rows exist, you can extract the joins from the SP to single queries and execute them seperatly.

select * from Roster where sID = @classid

select * from Scores where sID = ... (sID found in previous query)

select * from Assignments where AssignmentID = ... (AssignmentID found in previous query)

etc.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Mark-459099 SSC Eights! Points: 911 More actions · Answer 3

Thanks for responding. But I've figured it out without it being to technical. I just modified my query string a bit. I just added another condition to my WHERE clause. I added Final.Period=@period and it fixed the problem. Note to others who get duplicate values: The query string may not be written as sharply as possible. Thanks to everyone who responded.