I have table structure like this
Student
| Id | Student name |
|---|---|
| 1 | AAAA |
| 2 | BBBB |
Subject
| Id | Subject name |
|---|---|
| 1 | Subject1 |
| 2 | Subject2 |
Marks
| student_id | subject_id | marks |
|---|---|---|
| 1 | 1 | 35 |
| 1 | 2 | 34 |
| 2 | 1 | 54 |
| 2 | 2 | 42 |
Requirement is like, list all students with Pass (>=35 marks in all subject) or Fail (<35 marks in any subject) status. Any suggestion ?
Not sure what to add to if condition
select student.id, student.name, 'status (if marks.marks >= 35, Pass, Fail)'
from student
inner join marks on marks.student_id = student.id
Expected output
| student_id | student_name | status |
|---|---|---|
| 1 | AAAA | Fail |
| 2 | BBBB | Pass |
CodePudding user response：
Join Student to Marks and group by student.
Then use a CASE expression that checks the min marks of each student to get the status:
SELECT s.id student_id, s.student_name,
CASE WHEN MIN(m.marks) >= 35 THEN 'Pass' ELSE 'Fail' END status
FROM Student s INNER JOIN Marks m
ON m.student_id = s.id
GROUP BY s.id;
I assume that id is the primary key of the table Student.
If not, change the GROUP BY clause to:
GROUP BY s.id, s.student_name
See the demo