I am working in a web application using PHP and MySQL.
I have two tables
employee_id email_id 3 email@example.com 4 firstname.lastname@example.org 5 email@example.com 6 firstname.lastname@example.org 7 email@example.com
coaching_id emp_id start_date end_date 1 4 2014-05-01 2014-10-02 2 5 2014-12-18 2015-01-22
I want a query to find out the employees who are not enrolled for any coaching program between a specified date range such as Start Date "2015-01-22" and End Date "2015-03-12".
Now I am using query like this,
SELECT employee_id FROM employee WHERE sup_id = 3 AND employee_id NOT IN ( SELECT emp_id FROM (coaching) WHERE (start_date NOT BETWEEN "2014-12-26" AND "2015-01-30") AND (end_date NOT BETWEEN "2014-12-26" AND "2015-01-30") )
I have a logic, " If any employee already joined a coaching program at given start_date and end_date, he is not eligible for any coaching program.
For Example : If the start date is '2014-12-26' and end date is '2015-01-30'.
Then the employee with id 5 is not eligible for this coaching program as he is now on another coaching activity whose start date is '2014-12-18' and end date is '2015-01-22'.
But employee with id 4 is eligible as well as all other employees who are not in any coaching activity
Where is the issue in my query