Select users who fits in timetable mysql

Question

I need to select all users who "fits" in theirs working timetables.

Table in MySQL Timetable:

user_id                           PRIMARY
day_number(0-sunday 6-saturday)   PRIMARY (one user - one day)
start                             start worktime 
end                               end worktime

Sample user:

user_id    = 1
day_number = 1
start      = 10:00
end        = 18:00

user_id    = 1
day_number = 2
start      = 12:00
end        = 18:00

user_id    = 1
day_number = 3
start      = 14:00
end        = 16:00

Now I want select every user who works from 1(Monday) to 3(Wednesday) from 14:00 to 16:00. Sample user (with id 1) should be included.

Any Ideas?

Answer 1

SELECT user_id
FROM   my_table
  NATURAL JOIN (SELECT 1 day_number UNION ALL SELECT 2 UNION ALL SELECT 3) days
  JOIN (SELECT MAKETIME(14,0,0) start, MAKETIME(16,0,0) end) times
    ON my_table.start <= times.start
   AND my_table.end   >= times.end
GROUP BY user_id
HAVING   COUNT(DISTINCT my_table.day_number) = 3 -- number of days in range

See it on sqlfiddle.