Запрос на выборку с исключением по времени и по другой таблице

Question

есть две таблицы:

new_task

CREATE TABLE admin_website.new_task (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50) DEFAULT NULL,
  quantity int(11) DEFAULT NULL,
  status int(1) DEFAULT NULL,
  link varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
)

api_task_user

CREATE TABLE admin_website.api_task_user (
  id int(11) NOT NULL AUTO_INCREMENT,
  user_id int(11) DEFAULT NULL,
  id_task int(11) DEFAULT NULL,
  date datetime DEFAULT NULL,
  PRIMARY KEY (id)
)

В первой таблице находятся задания для пользователя.

Во второй информация о том, какой пользователь (user_id) выполнил задание (id_task) в какое время (date)

Допустим, в первой таблице сейчас находятся вот такие задания:

id  link                    quantity    status   link 
1   https://vk.com/         5000        1        https://vk.com/
2   https://yandex.com/     5000        1        https://yandex.com/
3   https://google.com/     3000        1        https://google.com/

Я буду пользователем с id = 1 и во второй таблице хранятся вот такие данные:

id      user_id  id_task date
4       1        1       Вт 31.03.20 17:28:47
5       1        2       Ср 24.06.20 17:29:36

Т.е. я выполнил первое и второе задание, но не выполнил третье.

Так же есть условия:

В выборку должны включаться записи, которые уже выполнил пользователь, но только через 24 часа

Задания у которых status = 1
Задания у которых quantity > 0

Набросал вот такой код:

SELECT
   * 
FROM
   new_task n 
   LEFT JOIN
      api_task_user atu 
      ON atu.id_task = n.id 
WHERE
   n.status = 1 
   AND n.quantity > 0 
   AND 
   (
      atu.user_id IS NULL 
      OR atu.user_id = 1
   )
   AND 
   (
      date < NOW() - INTERVAL 1 DAY 
      OR date IS NULL
   )
ORDER BY
   n.quantity

Он всё правильно отрабатывает до тех пор, пока не появляется пользователь 2 и начинает выполнять задания, которые ещё не выполнил я, тогда же пропадают записи, которые раньше выводила вот эта строка:

atu.user_id IS NULL

Потому что там уже не NULL, а id второго пользователя.

В общем вот и весь вопрос: Как сделать правильную выборку тех заданий, которые пользователь ещё не выполнил и включить туда те, которые от уже выполнил, но с того момента прошло 24 часа?

Версия сервера: MySQL 5.1.73

UPD:

Когда мой запрос выдал нужный результат, решил добавить новые задания, что бы проверить окончательно, что запрос работает правильно, добавил вот такую строку

id  link                    quantity    status   link 
4   https://asf.ru          2000        1        https://asf.ru

В итоге мой запрос не показывает, что это задание доступно, хотя должен был.

Запрос @timur-irdyneev Выдаёт следующее:

id  name            quantity   status  link             id
4   https://asf.ru  2000       1       https://asf.ru   (null)  ...
1   https://vk.com/ 5000       1       https://vk.com   4   ...

Что тоже не верно, поскольку нет задания:

id  name
3   https://google.com/

Скорее всего из-за того, что его выполнял пользователь 2

UPD 2:

--
-- Дата скрипта: Ср 24.06.20 17:18:09
-- Версия сервера: 5.1.73
-- Версия клиента: 4.1
--


SET NAMES 'AUTO';

INSERT INTO admin_website.api_task_user(id, user_id, id_task, date) VALUES
(4, 1, 1, '2020-03-31 17:28:47'),
(5, 1, 2, '2020-06-24 12:29:36'),
(13, 2, 3, '2020-03-31 17:28:47'),
(14, 2, 2, '2020-03-31 17:28:47'),
(17, 1, 5, '2020-06-24 12:29:36'),
(18, 2, 4, '2020-06-24 17:09:35'),
(19, 2, 5, '2020-03-31 17:28:47');


INSERT INTO admin_website.new_task(id, name, quantity, status, link) VALUES
(1, 'https://vk.com/', 5000, 1, 'https://vk.com/'),
(2, 'https://yandex.com/', 5000, 1, 'https://yandex.com/'),
(3, 'https://google.com/', 3000, 1, 'https://google.com/'),
(4, 'https://asf.ru', 2000, 1, 'https://asf.ru'),
(5, 'https://ok.com', 1000, 1, 'https://ok.com');

Answer 1

В общем, переделал запрос вот так и вроде как всё работает:

SELECT
   * 
FROM
   new_task nt 
   JOIN
      api_task_user a 
      ON nt.id = a.id_task 
WHERE
   (a.user_id  in 
   (
      SELECT
         user_id 
      FROM
         api_task_user atu 
      WHERE
         atu.user_id = 1
      ORDER BY atu.date  
        
   )
   OR a.id_task NOT in 
   (
      SELECT
         id_task 
      FROM
         api_task_user atu 
      WHERE
         atu.id_task IN (SELECT id_task FROM  api_task_user atu WHERE  atu.user_id = 1)
         ORDER BY atu.date 
   ))
AND a.date < NOW() - INTERVAL 1 DAY 

GROUP BY link

UPD 2:

Посмотрел, что наши ответы выдают друг друга исключающие варианты и решил их просто совместить и вот что из этого вышло:

SELECT DISTINCT link FROM  
(SELECT  link  FROM new_task nt
  LEFT JOIN api_task_user atu ON atu.id_task = nt.id
WHERE nt.status = 1
  AND (atu.id is null or atu.user_id = 1)
  AND (atu.date is null or atu.date < NOW() - INTERVAL 1 DAY)
GROUP BY link

UNION ALL 

SELECT
    link 
FROM
   new_task n 
   LEFT JOIN
      api_task_user atu 
      ON atu.id_task = n.id = atu.user_id = 1
WHERE
   n.status = 1 
   AND 
   (atu.user_id IS NULL )
   AND 
   (date < NOW() - INTERVAL 1 DAY OR date IS NULL)
GROUP BY link) AS fa

UPD Правильно работает только с первым пользователем, если изменить везде id_user = 2, результат выводится неверный, так что вопрос остаётся открытым

Answer 2

попробуйте такой вариант:

SELECT * FROM new_task nt
  LEFT JOIN api_task_user atu ON atu.id_task = nt.id
WHERE nt.status = 1
  AND nt.quantity > 0
  AND (atu.id is null or atu.user_id = 1)
  AND (atu.date is null or atu.date < NOW() - INTERVAL 1 DAY)
ORDER BY nt.quantity

UPD:2

select * from new_task nt
  LEFT JOIN (
      select * from api_task_user atu
      where atu.user_id=1 AND atu.date < NOW() - INTERVAL 1 DAY
  ) atu ON atu.id_task = nt.id
WHERE nt.status = 1
  AND nt.quantity > 0

UPD:3 с учетом последнего обновления описания

select * from new_task nt
  INNER JOIN (
    select * from api_task_user atu
    where atu.user_id is null
      or (atu.user_id = 1 AND atu.date < NOW() - INTERVAL 1 DAY)
  ) atu ON atu.id_task = nt.id
WHERE nt.status = 1
  AND nt.quantity > 0

БЛОГ НА HUSL

Запрос на выборку с исключением по времени и по другой таблице

Ответы (2 шт):