Help with SQL query please?

Question

Could i please get some help with another query please.

my tables are
Student ( (pk)student_id, student_name, teacher_id)
Student_Subject ( (pk)student_id, (pk) subject_id, mark)
subject ( (pk)subject_id, subject_name)

Okay I am trying to do a correlative subquery to produce a listing showing the MAXIMUM MARK obtained by each STUDENT (regardless of subject). Output needed, Student_id, student_name, subject_name and maximum mark.

i can get the student_id, student name and the maximum mark for each student, I am having problems just getting the subject_name slotted in.

this is my query so far

SELECT s1.student_id, s1.student_name, (select max(mark) from student_subject
where student_id = s1.Student_id) AS StudentMaxMark
FROM Student AS s1
WHERE EXISTS
(SELECT * FROM sTUDENT_SUBJECT S2
WHERE STUDENT_ID=S1.STUDENT_ID)
GROUP BY s1.student_id, s1.student_name;

everytime I try and get the subject_name involved i get too much output, this again is driving me nuts ta

Answer 1

The following query is what you are after



SELECT student_id, student_name,

(SELECT TOP 1 MAX(ss.mark)

FROM Student_Subject ss

WHERE (ss.student_id = s.student_id)

GROUP BY ss.subject_id

ORDER BY MAX(ss.mark) DESC) AS StudentMaxMark,

(SELECT TOP 1 subject.subject_name

FROM Student_Subject INNER JOIN

subject ON Student_Subject.subject_id = subject.subject_id

WHERE (Student_Subject.student_id = s.student_id)

GROUP BY subject.subject_name

ORDER BY MAX(Student_Subject.mark) DESC) AS StudentMaxSubject

FROM Student s





Please note that the above will give you the exact results you are after but it is not optimised to run on production. If you wanted to run this on a production type scenario, it would be best you created a proper prrimary key on the student_subject table. Then you could get the above results just using inner joins accross the three tables



Alternatively if you were forced to use the above structure, i would create a view for the inner sql that i had written so that it doesnt need to be computed twice.



Hope that helps.

Answer 2

This should work:



SELECT

a.student_id, a.student_name,

MAX(b.mark) AS maxmark,

c.subject_name

FROM Student a

INNER JOIN Student_Subject b ON a.student_id = b.student_id

INNER JOIN Subject c ON c.subject_id = b.subject_id

GROUP BY a.student_id, a.student_name, b.mark, c.subject_name, b.student_id, c.subject_id, b.subject_id



If you are using an earlier version of MySQL, you cannot use INNER JOINs. Instead, you use LEFT JOIN and exclude nulls from the right tables:



SELECT

a.student_id, a.student_name,

MAX(b.mark) AS maxmark,

c.subject_name

FROM Student a

LEFT JOIN Student_Subject b ON a.student_id = b.student_id

LEFT JOIN Subject c ON c.subject_id = b.subject_id

HAVING b.mark IS NOT NULL and c.subject_name IS NOT NULL

GROUP BY a.student_id, a.student_name, b.mark, c.subject_name, b.student_id, c.subject_id, b.subject_id

Answer 3

the ideal e book that taught me each and every little thing approximately MySQL is "MySQL Crash direction" by using Ben Forta. The e book is extremely undemanding, it shows the main-word, an occasion question, and an occasion output. i understand you're soliciting for sq., no longer MySQL. maximum sq. are comparable, and it truly is purely a question of understanding the few ameliorations of one and all. i might initiate right here.