What is wrong with my SQL Query?

[Stylesjl]

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.

 

[Grisu]

my SQL is a bit rusty, but something like this should work:

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

 

[Mise]

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.

 

[Grisu]

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

 

[Mise]

from the look of his query, that's the way he has it already

Yeah, just realised... was about to edit it out of my post and call myself a thicko!

 

[Stylesjl]

my SQL is a bit rusty, but something like this should work:

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

 

[warpus]

Yeah, if you're gonna group by and you need to pull in additional information that isn't compound (ie. sum, max, etc.) you gotta do a subquery