SQL Join multiple dismiss same values if one is false

Question

these are examples my tables:

Employees
id     | Name 
1      | employee 1   
2      | employee 2    
3      | employee 3    

Busy_Schedule
id     | start_date  | finish_date | employee_id
1      | 20-11-2017  | 22-11-2017  | 1 
2      | 24-11-2017  | 28-11-2017  | 1
3      | 20-11-2017  | 22-11-2017  | 2    
4      | 20-11-2017  | 22-11-2017  | 3

I want to choose available employees based on joining these two tables.

For example, I want to know who is available from 21-11-2017 to 23-11-2017. If I use join, I am getting employee 1 as available because of the multiple entries, whereas he is not available between these two dates.

I am working with Mysql

Thank you sirs. I was going to post my query but my question has been answered by Mr. Juan. Have a good day. — Allen, Nov 28 '17 at 19:14

score 0 · Accepted Answer · answered Nov 28 '17 at 18:36

0

You need to check if the dates ranges overlaps.

Determine Whether Two Date Ranges Overlap

So when you know what employees are busy on those days, just select the employees NOT IN that group.

  SELECT *
  FROM Employees 
  WHERE id NOT IN (SELECT employee_id
                   FROM Busy_Schedule
                   WHERE start_date  <= '2017-11-23'
                     AND finish_date >= '2017-11-21')

answered Nov 28 '17 at 18:36

Juan Carlos Oropeza

47,252
12
78
118

Great.Thank you sir, the answer was so simple while i was trying to complicate things. – Allen Nov 28 '17 at 19:11

SQL Join multiple dismiss same values if one is false

1 Answers1