do you remember the LEFT OUTER JOIN with IS NULL check?
SELECT emp.employeeid
FROM employees AS emp
LEFT OUTER
JOIN attendance_log AS att
ON att.emp_id = emp.employeeid
AND att.log_time = '2013-05-02'
WHERE emp.sub = 'REG'
AND emp.department IN ('QA', 'Engineering', 'Assembly')
AND att.emp_id IS NULL
SELECT emp.employeeid, emp.lastname, emp.firstname, emp.middlename, emp.department
FROM employees AS emp
LEFT OUTER
JOIN attendance_log AS att
ON att.emp_id = emp.employeeid
AND att.log_time = '2013-05-14'
WHERE emp.sub = 'REG'
AND emp.department = 'Assembly'
AND att.emp_id IS NULL;
and sad to say all data from employees where department is Assembly was displayed even they had an attendance.
This code is work to get the no IN and OUT for the particular date:
SELECT emp.employeeid, emp.lastname, emp.firstname, emp.middlename, emp.department
FROM employees AS emp
LEFT OUTER
JOIN attendance_log AS att
ON att.emp_id = emp.employeeid
AND DATE(att.log_time) = '2013-05-14'
WHERE emp.sub = 'REG'
AND emp.department IN ('Assembly', 'Fabrication', 'Compounding', 'Finishing', 'PET', 'Production', 'Squash', 'QA', 'ENGINEERING')
AND att.emp_id IS NULL;
now i need a query to get only the employee with IN and OUT indicator:
also a query which the employee has only IN or has only OUT.