I have three tables: student, assignment and subject. They have the following values:
Student
| student_id | student_name | subject_id |
|---|---|---|
| 2001 | John MacEnroe | 11 |
| 2002 | May Rockwell | 13 |
| 2005 | James Cronwell | 13 |
| 2012 | Jane Crickett | 14 |
Subject
| subject_id | subject_name |
|---|---|
| 11 | Math |
| 13 | History |
| 14 | Biology |
Assignments
| student_id | subject_id | description | score | submission_date |
|---|---|---|---|---|
| 2001 | 11 | assignment 1 | 8.0 | 2021-05-13 20:15:20 |
| 2001 | 11 | assignment 2 | 8.0 | 2021-06-15 21:35:40 |
| 2005 | 13 | assignment 1 | 4.0 | 2021-05-13 19:05:30 |
I want to create a query that returns to me a list of all the students and the related subjects, with the status on each subject following the rule below, and also the date of the last assignment evaluation.
- if there is no score = Pending
- if score >= 5 = Passed
- if score < 5 = Exam
Expected output:
| student_name | subject_name | description | status | submission_date |
|---|---|---|---|---|
| John MacEnroe | Math | assignment 1 | Passed | 2021-05-13 20:15:20 |
| John MacEnroe | Math | assignment 2 | Passed | 2021-06-15 21:35:40 |
| James Cronwell | History | assignment 1 | Exam | 2021-05-13 19:05:30 |
| May Rockwell | History | Pending | ||
| Jane Crickett | Biology | Pending |
I tried using the code below, with no avail. I got stucked. I aprecciate any help.
SELECT
student_name
,subject_name
,submission_date
from
student
,subject
,assignments;
CodePudding user response：
This is what you are looking for:
SELECT
s.`student_name`,
sub.`subject_name`,
IFNULL(a.`description`,'') description,
(CASE WHEN a.`score` IS NULL THEN 'Pending'
WHEN a.`score`>=5 THEN 'Passed'
ELSE 'Exam' END) `status`,
IFNULL(a.`submission_date` ,'') submission_date
FROM
`student` s
LEFT JOIN `Subject` sub ON s.`subject_id` = sub.`subject_id`
LEFT JOIN `Assignments` a ON a.`student_id` = s.`student_id`
Selecting from multiple tables without table relations will give you Cartesian product of all associated tables.