I want to display all the staffs in staffs table if any of the staffs in staff table is found in absent table and the staff start and end date is in between 2024-12-16 and 2024-12-20 it will display the data in col_status column in absent table and also if the staff start date is equal to 2024-12-16 and end date is equal to NFS it will also display data in col_status column on the staff.
My code
<code>SELECT
t1.userId,
t1.col_fullname,
t1.col_email,
t1.dateTime,
t2.col_status
FROM
staffs t1
LEFT JOIN
absent t2
ON
t1.userId = t2.userId
WHERE
(t2.col_start_date >= '2024-12-16' AND t2.col_end_date = '2024-12-20')
OR (t2.col_start_date = '2024-12-16' AND t2.col_end_date = 'TFN')
ORDER BY
t1.col_fullname;
</code>
<code>SELECT
t1.userId,
t1.col_fullname,
t1.col_email,
t1.dateTime,
t2.col_status
FROM
staffs t1
LEFT JOIN
absent t2
ON
t1.userId = t2.userId
WHERE
(t2.col_start_date >= '2024-12-16' AND t2.col_end_date = '2024-12-20')
OR (t2.col_start_date = '2024-12-16' AND t2.col_end_date = 'TFN')
ORDER BY
t1.col_fullname;
</code>
SELECT
t1.userId,
t1.col_fullname,
t1.col_email,
t1.dateTime,
t2.col_status
FROM
staffs t1
LEFT JOIN
absent t2
ON
t1.userId = t2.userId
WHERE
(t2.col_start_date >= '2024-12-16' AND t2.col_end_date = '2024-12-20')
OR (t2.col_start_date = '2024-12-16' AND t2.col_end_date = 'TFN')
ORDER BY
t1.col_fullname;
staffs table
absent table
This is the result I get
This is what I want
3