I want to find the students who are absent today. But I have a table named attendances where I kept only students who are present today. So that I have to join both students and attendances table to find which students are absent. Now I wrote a query which is resulted data twice.
My Query is:
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;
My Expected output
Could you try this?
SELECT A.student_name , A.student_phone , A.class_id, A.student_id FROM students AS A LEFT JOIN attendances AS B ON A.class_id = B.class_id AND A.student_id = B.student_id AND DATE(B.created_at) = '2023-04-05' WHERE A.company_id = 1 AND A.class_id = 1 AND B.entry_at IS NULL;