我想找到今天缺席的学生.但我有一张名为出勤率的桌子,我只接待了今天在场的学生.所以我必须同时加入学生表和出席表,才能找到哪些学生缺席了.现在,我编写了一个查询,结果是数据两次.
我的疑问是:
SELECT * FROM
(SELECT student_name,student_phone,class_id FROM `students` WHERE company_id=1 AND class_id=1) AS A
LEFT JOIN
(SELECT student_id,attendances.class_id,entry_at FROM `attendances` WHERE attendances.company_id=1 AND attendances.class_id=1 AND DATE(attendances.created_at)='2023-04-05')AS B
ON A.class_id=B.class_id;
student_name | student_phone | class_id | student_id | class_id | entry_at |
---|---|---|---|---|---|
Mizbah | 0123224 | 1 | 001234 | 1 | 10:24:41 |
Farid | 0125321 | 1 | 005321 | 1 | 10:24:41 |
Mizbah | 0123224 | 1 | 001234 | 1 | 10:31:17 |
Farid | 0125321 | 1 | 005321 | 1 | 10:31:17 |
我的预期yields
student_name | student_phone | class_id | student_id | class_id | entry_at |
---|---|---|---|---|---|
Mizbah | 0123224 | 1 | 001234 | 1 | 10:24:41 |
Farid | 0125321 | 1 | 005321 | 1 | 10:31:17 |