MySQL selecting row for attendance case

Question

I have these tables:

table 1 : attendance
-------------------------------
ID |   DATE     | EMPLOYEE_ID | 
-------------------------------
1   2013-09-10        1
2   2013-09-10        2
3   2013-09-10        3
-------------------------------


table 2: employee
---------------
ID | NAME     |
---------------
1    Smith
2    John
3    Mark
4    Kyle
5    Susan
6    Jim
---------------

My actual code to show employee option.

while($row=mysql_fetch_array($query)){
    echo "<option value='$row[employee_id]'>$row[first_name] $row[last_name]</option>";
}
?>

How can i show the list of employee that not registered in table 1? The condition is if an employee already registered in table 1, they won't appear on the option. I want to show the list in <option>'s of <select> element. So it will return: kyle, susan, jim.

Please tell me the correct query or if there is any better option, it'll be good too. Please give some solution and explain. Thank you very much

UPDATE / EDIT:

it also based on current date, if in table 1 have no latest date e.g. today it's 2013-09-15. It will show all of employee.

Answer 1

You can do this with a left join and then checking for no matches:

select e.*
from employee e left outer join
     attendance a
     on e.id = a.employee_id
where a.employee_id is null;

This is probably the most efficient option in MySQL.

EDIT:

To include a particular date, add the condition to the on clause:

select e.*
from employee e left outer join
     attendance a
     on e.id = a.employee_id and a.date = date('2013-09-20')
where a.employee_id is null;