MySQL Query to find records between two date range

Question

0.00/5 (No votes)

See more:

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,

SQL

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"))

Where is the issue in my query

Thanks

Posted 26-Dec-14 4:50am

Bikram Keshari Pahi

Updated 27-Dec-14 9:55am

King Fisher

v3

Add a Solution

Comments

PIEBALDconsult 26-Dec-14 11:11am

Perhaps you have too many NOTs in your code?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-12-27T08:55:00

Solution 1

Try this:

SQL

SELECT e.employee_id
FROM employee AS e INNER JOIN coaching AS c ON e.employee_id = c.emp_id
WHERE e.sup_id = 3 AND c.start_date NOT BETWEEN "2014-12-26" AND "2015-01-30" AND c.end_date NOT BETWEEN "2014-12-26" AND "2015-01-30"

I'd suggest to read this: Visual Representation of SQL Joins[^]

Posted 27-Dec-14 8:55am

Maciej Los

Comments

King Fisher 27-Dec-14 15:55pm

mt 5+

Maciej Los 27-Dec-14 15:57pm

Thank you, King ;)
Happy Christmas to you ;)

King Fisher 27-Dec-14 16:00pm

thank you ..you too and your Family :0

Maciej Los 27-Dec-14 16:01pm

Thank you ;)

DamithSL · Answer 2 · 2014-12-27T17:17:00

Quote:
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".

that include employees who haven't enrolled any program plus employees not enrolled between specified period. You can do this using LEFT Join

SQL

SELECT e.employee_id
FROM Employee e
LEFT JOIN Coaching c
ON e.employee_id = c.emp_id
WHERE c.emp_id IS NULL or  
 not (c.start_date  BETWEEN '2014-12-26' AND '2015-01-30' 
        or c.end_date  BETWEEN '2014-12-26' AND '2015-01-30')

DEMO[^]