Reputation: 35
I have tables as below
employees
-----------------------
empId Name MobileNumber
1 Name1 12345
2 Name2 123456
3 Name3 1234567
4 Name4 12345678
attendance
-------------------------------------------
Id MobileNumber Type attendance_datetime
1 12345 IN 2017-05-31 10:04:42
2 123456 IN 2017-05-31 10:05:29
3 1234567 IN 2017-05-31 10:06:15
4 12345 OUT 2017-05-31 18:05:15
5 123456 OUT 2017-05-31 18:10:15
6 1234567 OUT 2017-05-31 18:15:15
I want to get absent user list for a particular date how to get it ?
I have tried below query but not gettting result.
select * from employees e,attendance a where e.MobileNumber = a.MobileNumber AND DATE(a.attendance_datetime) = str_to_date('31-05-2017', '%d-%m-%Y')
Upvotes: 2
Views: 492
Reputation: 1849
You need to use left outer join:
SELECT * FROM employees e
LEFT JOIN attendance ON employees.MobileNumber = attendance.MobileNumber
AND DATE(attendance_datetime) = '2017-05-31'
WHERE attendance.Id IS NULL
Upvotes: 2