Problem joining the result - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem joining the result

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-09-20 : 14:52:52

Hi,

Here is my situation. I am making a school system in which I need to retrieve the max score of a student in a given test. This means that the student can give the same exam multiple times. The query must also return the range in which his result lies.

This means if I give the same exam 3 times and my highest score is 89 then I belong to the range 76-100. All ranges are stored in the database table "UserScoreGroup".

Everything is working fine but the main problem is that when I return the student and if that student has given the same exam 3-4 times and in each exam score lies in different range (Range = Group) then the query is returning all the groups and the exams. But I only want to return the exam and group in which his score was the highest.

Here are my tables.

CREATE TABLE [dbo].[Exams] (
[ExamID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserExams] (
[UserExamID] [int] IDENTITY (1, 1) NOT NULL ,
[ExamID] [int] NOT NULL ,
[UserID] [int] NOT NULL ,
[Score] [float] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserScoreGroups] (
[UserScoreGroupID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LowerBound] [float] NOT NULL ,
[UpperBound] [float] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

And here is my query which returns duplicate data for the students who have score in different ranges.

DECLARE @ExamID int
SET @ExamID = 1

SELECT ue.ExamID,MAX(ue.Score) AS Score,u.FirstName,u.LastName,e.Title,g.Title AS [Group] FROM UserExams ue
JOIN Users u ON u.UserID = ue.UserID
JOIN Exams e ON e.ExamID = ue.ExamID
JOIN UserScoreGroups g ON ( ue.Score BETWEEN g.LowerBound AND g.UpperBound )
WHERE ue.ExamID = @ExamID
GROUP BY ue.ExamID,u.FirstName,u.LastName,e.Title,g.Title

Thanks,
Azam

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-09-20 : 14:54:50

It if helps in anyway this is the output I get when I run the query provide in the previous post.

ExamID Score FirstName LastName Title Group
----------- ----------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1 12.0 Clark Kent Exam 1 1 - 25
1 87.0 Jerry Seinfeld Exam 1 76 - 100
1 78.0 John Doe Exam 1 76 - 100
1 98.0 Mary kate Exam 1 76 - 100
1 45.0 Mohammad Azam Exam 1 26 - 50
1 67.0 Mohammad Azam Exam 1 51 - 75

(6 row(s) affected)

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-09-20 : 16:29:18

Any one please!!

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-09-20 : 16:46:15

Well I hardcoded the solution but it is not good. I don't like it.

DECLARE @ExamID int
SET @ExamID = 1

SELECT MAX(ue.Score) AS Score,u.FirstName,u.LastName,e.Title,
'Group' =
CASE
WHEN MAX(ue.Score) BETWEEN 0 AND 59 THEN '0 - 59'
WHEN MAX(ue.Score) BETWEEN 60 AND 69 THEN '60 - 69'
WHEN MAX(ue.Score) BETWEEN 70 AND 79 THEN '70 - 79'
WHEN MAX(ue.Score) BETWEEN 80 AND 89 THEN '80 - 89'
WHEN MAX(ue.Score) BETWEEN 90 AND 100 THEN '90 - 100'
END

FROM UserExams ue
JOIN Users u ON u.UserID = ue.UserID
JOIN Exams e ON e.ExamID = ue.ExamID
WHERE ue.ExamID = @ExamID
GROUP BY u.UserID,u.FirstName,u.LastName,e.Title

Mohammad Azam
www.azamsharp.net

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-20 : 18:25:44

it would much help if you could also provide some sample data to work on. And don't forget the result that you want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources