mysql - 查找学生参加和未参加的类(class)

Question

我想知道该学生参加了哪些类(class)。这意味着获取已发生的所有类(class)的列表，并将其与学生是否参加此类(class)进行比较。
我有3张 table :出勤记录

| session_id | student_id |    date    |   time   |
+------------+------------+------------+----------+
|         52 |   17000004 | 2017-03-07 | 16:05:02 |
|         52 |   17000004 | 2017-03-14 | 16:05:02 |
|         52 |   17000004 | 2017-03-21 | 16:05:02 |
|         52 |   17000004 | 2017-03-28 | 16:05:02 |
|         52 |   17000004 | 2017-04-04 | 16:05:02 |
|         52 |   17000004 | 2017-04-11 | 16:05:02 |
|         52 |   17000004 | 2017-05-02 | 16:05:02 |
+------------+------------+------------+----------+`  

session

+------------+-----------+----------+---------+--------------+-------------+
| session_id | module_id | staff_id | room_id |  day_of_week |  type       |  
+------------+-----------+----------+---------+--------------+-------------+
|         52 |         8 | 17000004 |      11 | Tuesday      | Lecture     |  
|         53 |         8 | 17000004 |      12 | Friday       | Lab         |  
|         53 |         8 | 17000004 |      12 | Friday       | Lab         |  
+------------+-----------+----------+---------+--------------+-------------+`  

学生

+----------+---------+---------------------+--+
|   s_id   | s_name  |       s_email       |  |
+----------+---------+---------------------+--+
| 17000004 | student | student@student.com |  |
+----------+---------+---------------------+--+

预期输出

+------------+---------+
|    date    | attended|
+------------+---------+
| 2017-02-14 |       0 |
+----------+-----------+
| 2017-02-21 |       0 |
+----------+-----------+
| 2017-02-28 |       0 |
+----------+-----------+
| 2017-03-07 |       1 |
+----------+-----------+
| 2017-03-14 |       1 |
+----------+-----------+
| 2017-03-21 |       1 |
+----------+-----------+
| 2017-03-28 |       1 |
+----------+-----------+

我试图弄清楚的查询是:
查找 module_id = 8 且 session.type = 'Lecture' 的模块发生的所有日期并查看该学生是否参加了该类(class)。

Answer 1

第一步应该是加入表:

SELECT * from attendance_record as a inner join session as s 
  on a.session_id = s.session_id 
  inner join student as st 
  on a.student_id = st.s_id

在此你可以查询任何你想要的内容

SELECT * from attendance_record as a inner join session as s 
  on a.session_id = s.session_id 
  inner join student as st 
  on a.student_id = st.s_id
  WHERE
  s.module_id = 7 
  AND 
  s.session_type = 'Lecture'
  AND 
  st.student_name = 'student'

如果您只想知道他是否参加，只需使用 count(*) 看看您是否得到超过 0