MYSQL query to find records between two date range

Question

I am working in a web application using PHP and MySQL.

I have two tables

1. Employee
   

   employee_id email_id
   3 [email protected]
   4 [email protected]
   5 [email protected]
   6 [email protected]
   7 [email protected]
   

2. Coaching
   

   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

Thanks

Answer 1

Your query has a logical flaw: it tries to find people who are NOT in the list of people who DO have coaching but only if the coaching is NOT in the target window.

Instead, join the employee and coaching table on employee_id, using all rows in employee but only using rows in coaching where the stop_date is later than or equal to the desired start date (coaching ends after the window starts) and the the start_date is less than or equal to the desired stop date (coaching starts before the window ends). Then exclude the employees with one or more matching rows in coaching.

SELECT e.id AS employee_id
 FROM employee e
 LEFT JOIN coaching c ON c.employee_id = e.id
 AND c.end_date >= '2014-12-26'
 AND c.start_date <= '2015-01-30'
 WHERE c.employee_id IS NULL;

Live demo: http://sqlfiddle.com/#!9/79b39/4

• Bikram Pahi: your answer is good but doesn't meet my requirement. You can see my question's table data. 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.
• Michael - sqlbot: The answer has been updated with the logic I intended to express earlier and the correct rows are now returned.