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

>Solution :

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;