SQL - Compare 2 rows with similar primary key constrains

user_16645902 0
Viewed 33

There's a student, course, and score_sheet table. score_sheet table has primary key constraints made up of the student id, and the course id. I want to get a student record where their course_1 has higher marks than course_2.

Student Table

student_id name age
s001 John 17
s002 Claire 18
s003 Maggie 19

Course Table

course_id name
c001 Math
c002 Art
c003 P.E.

Score Sheet Table

student_id course_id score
s001 c001 78
s001 c002 86
s001 c003 94
s002 c001 90
s002 c002 75
s002 c003 76
s003 c001 65
s003 c002 96
s003 c003 82

From the tables above, Claire is the only student satisfying the condition, course_1 has higher marks than course_2.

I'm not sure how to write the query into getting such records.

I've only managed to Inner Join the student table with the score_sheet table, and not sure where to go from here.

SELECT * FROM student s
INNER JOIN score_sheet sc
    ON s.SNO = sc.SNO
mysql sql
1 Answers

Join twice with score_sheet, once for each course you want to match.

SELECT s.*
FROM student AS s
JOIN score_sheet AS ss1 ON s.student_id = ss1.student_id
JOIN score_sheet AS ss2 ON s.student_id = ss2.student_id AND ss1.score > ss2.score
WHERE ss1.course_id = 'c001' AND ss2.course_id = 'c002'
user_14918950
Related