mySQL -连接多个表

Question

我有下面显示的表格。我正在使用它作为我的android测验应用程序。我想去接那些不能参加测验的学生。

表格测验

+-------------------------------------------------------------------+
| id | title  |   start    |    end     | class_name | user_faculty |
+-------------------------------------------------------------------+
| 1  | Quiz 1 | 2016-08-01 | 2016-08-03 | T4         | faculty_1    |
+-------------------------------------------------------------------+
| 2  | Quiz 2 | 2016-08-01 | 2016-08-03 | T4         | faculty_1    |
+-------------------------------------------------------------------+
| 3  | Quiz 3 | 2016-08-03 | 2016-08-04 | T4         | faculty_1    |
+-------------------------------------------------------------------+

表学生班

+-----------------------------------------------+
| id | class_name | user_faculty | user_student |
+-----------------------------------------------+
| 1  | T4         | faculty_1    | student_1    |
+-----------------------------------------------+
| 2  | T4         | faculty_1    | student_2    |
+-----------------------------------------------+

我在这里的初始查询是：

SELECT Q.id, Q.title, Q.start, Q.end
FROM quiz Q
INNER JOIN studentclass SC
ON Q.class_name = SC.class_name
AND Q.start_on <= now() 
AND Q.end_on >= now()
AND SC.user_student = 'student_1'

假设今天的日期是2016-08-02

我能够获取测验表的第一行和第二行

+---------------------------------------+
| id | title  |   start    |    end     |
+---------------------------------------+
| 1  | Quiz 1 | 2016-08-01 | 2016-08-03 |
+---------------------------------------+
| 2  | Quiz 2 | 2016-08-01 | 2016-08-03 |
+---------------------------------------+

现在，我还想获取分数表以及前面的查询。一个叫“student_1”的学生完成了测验。

表分数

+--------------------------------------------------------+
| id | score | grade | user_student | quiz_id | status   |
+--------------------------------------------------------+
| 1  | 10    | 100   | student_1    | 1       | Finished |
+--------------------------------------------------------+

我的新查询

SELECT Q.id, Q.title, Q.start, Q.end, S.user_student, S.status
FROM quiz Q
INNER JOIN studentclass SC
ON Q.class_name = SC.class_name
AND Q.start_on <= now() 
AND Q.end_on >= now()
AND SC.user_student = 'student_1'
LEFT JOIN score S
ON Q.id = S.quiz_id
WHERE S.quiz_id IS NULL

结果是

+---------------------------------------------------------------+
| id | title  |   start    |    end     | user_student | status |
+---------------------------------------------------------------+
| 2  | Quiz 2 | 2016-08-01 | 2016-08-03 | NULL         | NULL   |
+---------------------------------------------------------------+

结果很好，但当我在第二个查询中将student_1更改为student_2时，我仍然得到相同的结果。student_2应该会看到两个测验行，因为他还没有参加测验。

SELECT Q.id, Q.title, Q.start, Q.end, S.user_student, S.status
FROM quiz Q
INNER JOIN studentclass SC
ON Q.class_name = SC.class_name
AND Q.start_on <= now() 
AND Q.end_on >= now()
AND SC.user_student = 'student_2' //this is where I changed the student_1 to student_2
LEFT JOIN score S
ON Q.id = S.quiz_id
WHERE S.quiz_id IS NULL

感谢您的回答

Answer 1

问题似乎是，当加入SCORE表时，您没有检查哪个学生参加了测验。因此，如果测验是由任何学生完成的，则测验将从结果中排除。

尝试更改查询，如下所示：

SELECT Q.id, Q.title, Q.start, Q.end, S.user_student, S.status
FROM quiz Q
INNER JOIN studentclass SC
ON Q.class_name = SC.class_name
AND Q.start_on <= now() 
AND Q.end_on >= now()
LEFT JOIN score S
ON Q.id = S.quiz_id
AND SC.user_student = S.user_student   -- Do not consider other students 
WHERE S.quiz_id IS NULL
AND SC.user_student = 'student_2'      -- moved from join clause for clarity