MYSQL query to find records between two date range

join;MySQLmysql-5.5subquery

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

I have two tables

  1. Employee 

    employee_id     email_id
3             tl1@jasmine.com
4             agent1@jasmine.com
5             agent2@jasmine.com
6             om1@jasmine.com
7             tl2@jasmine.com

  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

Best Answer

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

Related Question