user2431727 user2431727 - 5 months ago 10
SQL Question

Join three tables with MAX function

I have three tables student,subject,score. I want to display the details of max(subject_id) of each student.

student table

student_id student_name exam_date
1 Sharukh 24/06/12
2 Amir 23/06/12


subject table

subject_id sub_name
200 Maths
300 English
400 Science


score table

student_id subject_id score
1 200 50
1 300 20
2 300 10


The result should be:

student_id student_name subject_id score
1 Sharukh 300 20
2 Amir 300 10

Answer

Use the MAX function and GROUP BY your other selections.

SELECT st.student_id, st.student_name, MAX(su.subject_id) AS subject_id, sc.score
FROM student st
INNER JOIN score sc ON st.student_id = sc.student_id
INNER JOIN subject su ON sc.subject_id = su.subject_id
WHERE su.subject_id = (SELECT MAX(sca.subject_id) 
                       FROM score sca 
                       WHERE sc.student_id = sca.student_id 
                       GROUP BY sca.student_id)
GROUP BY st.student_id, st.student_name, sc.score

Output:

student_id  student_name  subject_id  score
1           Sharukh       300         20
2           Amir          300         10

SQL Fiddle: http://sqlfiddle.com/#!9/71c46a/7/0

Without the GROUP BY

SELECT st.student_id, st.student_name, su.subject_id, sc.score
FROM student st
INNER JOIN score sc ON st.student_id = sc.student_id
INNER JOIN subject su ON sc.subject_id = su.subject_id
WHERE su.subject_id = (SELECT MAX(sca.subject_id) 
                       FROM score sca 
                       WHERE sc.student_id = sca.student_id 
                       GROUP BY sca.student_id)
Comments