CODEWITHSUNDEEP
mysqlsqljoin
Abdul Rahman
Abdul Rahman

Reputation: 1705

daily attendance to show all the employees whom attendance is not exist in another table?

I have two tales (MySql) employees and daily_attendance, where in daily_attendance i am trying to save daily attendance of every employee. I have the following columns in daily_attendance table:

sno, (primary key)
emp_sno, (sno from employees table primary key in employees table)
prj_sno,
emp_sup_sno,
duty_status,
duty_year,
duty_month,
reason,
duty_day,
is_current

i need to show an attendance form where user will save daily attendance in the daily_attendence table by clicking on the "A" Absent or "P" Present radio button. The user should need to see only those employees whom attendance has not already saved in the selected year, month and day. for this i have written the following query but it does not working for me.

SELECT * FROM employees e
LEFT JOIN daily_attendance d
ON e.sno = d.emp_sno
WHERE d.emp_sno IS NULL AND d.duty_year = '2013' AND d.duty_month = '1' 
AND d.duty_day = '1' 
AND d.is_current = 1

Now, i am not sure is it the good way to show all the employees list whom attendance not yet been added in daily_attendance table?

Please help me in this. thanks in advance

Upvotes: 0

Views: 896

Answers (1)

peterm
peterm

Reputation: 92785

Try it this way

SELECT e.* 
  FROM employees e LEFT JOIN daily_attendance d
    ON e.sno = d.emp_sno
   AND d.duty_year = '2013' 
   AND d.duty_month = '1' 
   AND d.duty_day = '1' 
   AND d.is_current = 1
 WHERE d.emp_sno IS NULL

Upvotes: 2

Related Questions