Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Joined two select statements but data is showing twice instead of once

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;
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

My Expected output

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

>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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading