2

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

5

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

  • 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. – Bikram Pahi Dec 26 '14 at 15:26
  • The answer has been updated with the logic I intended to express earlier and the correct rows are now returned. – Michael - sqlbot Dec 26 '14 at 22:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.