MYSQL - Query users that does not have a task between 2 date time parameters

user_822465 0
Viewed 32

I have this table in my DB.

users
- id
- name

tasks
- id
- start (datetime)
- end (datetime)

task_users
- id
- user_id

user_roles
- id
- user_id
- role_id (3 = crew leader OR 1 = regular)

I want to query users that are crew leaders and don't have a task between 2 given dates.

This is my query, but I am getting the wrong result. It is still showing users that have a task on those date-time payload:

SELECT users.* from users 
LEFT JOIN task_users ON users.id = task_users.user_id
LEFT JOIN tasks ON task_users.task_id = tasks.id
LEFT JOIN user_roles ON users.id = user_roles.user_id
WHERE
((tasks.start NOT BETWEEN '2022-01-10 00:00:00' AND '2022-01-10 00:30:00') OR (tasks.end NOT BETWEEN '2022-01-10 00:00:00' AND '2022-01-10 00:30:00'))
AND
users.subscriber_id = 2 AND users.deleted_at IS NULL AND users.termination IS NULL AND (users.archive = 0 OR users.archive IS NULL)
AND
user_roles.role_id = 3
GROUP BY users.id

What seems to be the issue in my query?

This is the screenshot of the query that I am getting wrong results. enter image description here

As you can see user.id 56 is in the result, but 56 has a task between those 2 date-time parameter. See next screenshot: enter image description here

task_id 2967 is between 2022-01-10 00:00:00 AND 2022-01-10 00:30:00. See screenshot: enter image description here

Please let me know if you need more information from me? Sorry for the lack of clarity.

Thanks and your help is greatly appreciated!

PS: The DB Table columns that I showed you are just the columns that I think are important for my question. I still have other columns as you noticed in my query.

laravel mysql php
1 Answers

You can break the problem into a few parts:

  1. Which users have tasks in the time period

  2. Which users are the crew leaders

  3. Which crew leaders are not in (1) above

Users with tasks

select distinct user_id 
from task_users 
join tasks on task_users.task_id = tasks.id
where 
  tasks.start >= '2022-01-10 00:00:00' and 
  tasks.end <= '2022-01-10 00:30:00'

Crew leaders

select user_id 
from user_role
where role_id = 3

Crew leaders with no tasks

select * 
from users 
where user_id in (
  select user_id 
  from user_role
  where role_id = 3 
) and user_id not in (
  select distinct user_id 
  from task_users 
  join tasks on task_users.task_id = tasks.id
  where 
    tasks.start >= '2022-01-10 00:00:00' and 
    tasks.end <= '2022-01-10 00:30:00'
)
user_23084730
Related