i cant view all data when i using Datepicker on ‘WHERE’

hy, please help me,, i cant view all data from emp0003 to checking all employee absence..

emp0003 table

NIK	Name
01190301	Susan
01190302	Jamed

dvc0004 table’s

NIK	Enroll
01190301	2021-02-03 08:06:27
01190302	2021-02-02 18:52:15
01190302	2021-02-02 08:02:32
01190302	2021-02-01 20:07:13
01190301	2021-02-01 20:07:13
01190302	2021-02-01 07:55:49
01190301	2021-02-01 07:55:49

this is my code :

SELECT
            emp0003.NIK,
            emp0003.`Name`,
            IF(emp0003.NIK=dvc0004.NIK,'Attended','Not Attended') as Attendance
            FROM
            emp0003
            left JOIN
            dvc0004
            ON
            emp0003.NIK = dvc0004.NIK
            WHERE
            DATE(dvc0004.Enroll) = '2021-02-02'

and the result my code :

NIK	Name	Attendance
01190302	Jamed	Attended

The result should be :

NIK	Name	Attendance
01190302	Susan	Not Attended
01190302	Jamed	Attended

please help me

Answer

You need to include the date condition on dvc0004 into the JOIN condition, otherwise it converts the join to an inner join, which is why you get no results for Susan. Also, you need to use either SELECT DISTINCT or a GROUP BY clause to remove duplicate rows (since in your sample data Jamed enrolled twice on 2021-02-02). I’ve gone with SELECT DISTINCT:

SELECT DISTINCT 
       emp0003.NIK,
       emp0003.`Name`,
       CASE WHEN dvc0004.NIK IS NULL THEN 'Not Attended' ELSE 'Attended' END as Attendance
FROM emp0003
LEFT JOIN dvc0004 ON emp0003.NIK = dvc0004.NIK AND DATE(dvc0004.Enroll) = '2021-02-02'

Output:

NIK         Name    Attendance
1190302     Jamed   Attended
1190301     Susan   Not Attended

Demo on dbfiddle