What is wrong with my SQL Query?

Stylesjl

SOS Brigade Member
Joined
Apr 30, 2005
Messages
3,698
Location
Australia
Spoiler :
SELECT Subject_Name, SS.Subject_ID, SS.Student_ID, Student_Name, Max(Mark)
FROM Subject AS SU, Student_Subject AS SS, Student AS S
WHERE Mark Is Not Null And SS.Subject_ID=SU.Subject_ID And S.Student_ID=SS.Student_ID
GROUP BY Subject_Name, SS.Subject_ID, SS.Student_ID, Student_Name
ORDER BY SS.Subject_ID;


Right now when I run this I get duplicate results for each 'Subject' when what I intend to do is get the top mark of each subject and then the name of the student who achieved it. How would I go about getting it to do that? I feel like I have done something incorrectly with the Table Join in the WHERE clause, but I don't know for sure.
 
my SQL is a bit rusty, but something like this should work:

Code:
SELECT SS.subject_ID, SU.SUBJECT_NAME, SS.STUDENT_ID, ST.student_name, SS.mark
FROM STUDENT_SUBJECT AS SS, STUDENT AS ST, SUBJECT AS SU
WHERE  ss.mark =  (SELECT MAX(MARK) FROM STUDENT_SUBJECT AS sub WHERE sub.SUBJECT_ID = SS.SUBJECT_ID)
AND SS.STUDENT_ID = ST.STUDENT_ID AND SS.SUBJECT_ID = SU.SUBJECT_ID
 
I don't understand what you've done with the tables.

I think you should have a table that has SubjectID, StudentID, and Mark. You should then have another table with SubjectID and SubjectName, and a third for StudentID and StudentName.

What are the contents of your tables currently? I'm guessing, from your problem, that you have an SU table with SubjectID, StudentID, SubjectName and Mark in it. I don't think that will work.
 
I think you should have a table that has SubjectID, StudentID, and Mark. You should then have another table with SubjectID and SubjectName, and a third for StudentID and StudentName.

from the look of his query, that's the way he has it already :)
 
my SQL is a bit rusty, but something like this should work:

Code:
SELECT SS.subject_ID, SU.SUBJECT_NAME, SS.STUDENT_ID, ST.student_name, SS.mark
FROM STUDENT_SUBJECT AS SS, STUDENT AS ST, SUBJECT AS SU
WHERE  ss.mark =  (SELECT MAX(MARK) FROM STUDENT_SUBJECT AS sub WHERE sub.SUBJECT_ID = SS.SUBJECT_ID)
AND SS.STUDENT_ID = ST.STUDENT_ID AND SS.SUBJECT_ID = SU.SUBJECT_ID

I made some modifications (cleaning up some unneeded aliases, adding an order by and putting most of the fields in camel case), but your query nonetheless works! Thanks

Spoiler :
SELECT SS.Subject_ID, Subject_Name, SS.Student_ID, S.Student_Name, Mark
FROM Student_Subject AS SS, Student AS S, Subject AS SU
WHERE mark = (SELECT MAX(MARK) FROM Student_Subject AS sub WHERE sub.Subject_ID = SS.Subject_ID)
AND SS.Student_ID = S.Student_ID AND SS.Subject_ID = SU.Subject_ID
ORDER BY SS.Subject_ID
 
Back
Top Bottom