SQL request excluding periods of time

Question

I need to get all DISTINCT users excluding those who are not available according to unavailability periods of time.

The user table:

+------+-----------+--------------------------------------+
| id   | firstname | content                              |
+------+-----------+--------------------------------------+
| 13   | John      | ...                                  |
| 44   | Marc      | ...                                  |
| 55   | Elise     | ...                                  |
+------+-----------+--------------------------------------+

The unavailability periods table:

+------+-----------+--------------+--------------+
| id   | user_id   | start        | end          |
+------+-----------+--------------+--------------+
| 1    | 13        | 2019-07-01   | 2019-07-10   |
| 2    | 13        | 2019-07-20   | 2019-07-30   |
| 3    | 13        | 2019-09-01   | 2019-09-30   |
| 4    | 44        | 2019-08-01   | 2019-08-15   |
+------+-----------+--------------+--------------|

For example, we want user who are available from 2019-06-20 to 2019-07-05: Marc and Elise are available.

Do I have to use a LEFT JOIN? This request is not working:

SELECT DISTINCT user.*, unavailability.start, unavailability.end,
FROM user
LEFT JOIN unavailability ON unavailability.user_id = user.id
WHERE  
unavailability.start < "2019-06-20" AND unavailability.end > "2019-06-20"
AND unavailability.start < "2019-07-05" AND unavailability.end > "2019-07-05"

And I need as result:

+------+-----------+--------------------------------------+
| id   | firstname | content                              |
+------+-----------+--------------------------------------+
| 44   | Marc      | ...                                  |
| 55   | Elise     | ...                                  |
+------+-----------+--------------------------------------+

With this request I don't get Elise who has no unavailability periods of time.

Answer 1

You can select the ids of the unavailables and use this result in a subquery :

Schema (MySQL v5.7)

CREATE TABLE user (
  `id` INTEGER,
  `firstname` VARCHAR(5),
  `content` VARCHAR(3)
);

INSERT INTO user
  (`id`, `firstname`, `content`)
VALUES
  (13, 'John', '...'),
  (44, 'Marc', '...'),
  (55, 'Elise', '...');



CREATE TABLE unavailability (
  `id` INTEGER,
  `user_id` INTEGER,
  `start` DATETIME,
  `end` DATETIME
);

INSERT INTO unavailability
  (`id`, `user_id`, `start`, `end`)
VALUES
  (1, 13, '2019-07-01', '2019-07-10'),
  (2, 13, '2019-07-20', '2019-07-30'),
  (3, 13, '2019-09-01', '2019-09-30'),
  (4, 44, '2019-08-01', '2019-08-15');

---

Query #1

SELECT *
FROM user us
WHERE us.id NOT IN (
  SELECT u.user_id
  FROM unavailability u
  WHERE u.start <= '2019-07-05' AND u.end >= '2019-06-20'
);

| id  | firstname | content |
| --- | --------- | ------- |
| 44  | Marc      | ...     |
| 55  | Elise     | ...     |

---

View on DB Fiddle

---

Note

This condition :

unavailability.start < 2019-06-20 AND unavailability.end > 2019-06-20
AND unavailability.start < 2019-07-05 AND unavailability.end > 2019-07-05

Will be evaluated like this :

unavailability.start < 2019-06-20 AND unavailability.end > 2019-07-05

Because, for the parts unavailability.start < 2019-06-20 AND unavailability.start < 2019-07-05, everything below 2019-07-05 but above 2019-06-20 will be excluded (you are using AND). The same for both unavailability.end