I have 2 tables: users and users_arrivals, I need to create a list of users who was in work in specific ranges of dates. Users table looks like:
| id | firstName | lastName |
| -- | --------- | -------- |
| 1 | John 1 | Test 1 |
| 2 | John 2 | Test 2 |
| 3 | John 3 | Test 3 |
| 4 | John 4 | Test 4 |
users_arrivals:
| id | user_id | start_date | end_date |
| -- | ------- | ---------- | ---------- |
| 1 | 1 | 2022-09-01 | 2022-09-30 |
| 2 | 2 | 2022-09-22 | 2022-09-25 |
| 3 | 3 | 2022-09-19 | 2022-09-25 |
And now I need to get all users who was in work between dates 2022-09-19 and 2022-09-25 but some users have range 2022-09-01 and 2022-09-30 and also should be returned. I tried to make SQL like that but it only returns users who have range smaller or same as 2022-09-19 and 2022-09-25. How I can get users who have bigger range?
SELECT *
FROM users u
LEFT JOIN users_arrivals po ON po.user_id = u.id
WHERE po.start_date BETWEEN '2022-09-19' AND '2022-09-25' AND po.end_date BETWEEN '2022-09-19' AND '2022-09-25'
GROUP BY u.id
CodePudding user response：
Maybe something like this:
SELECT *
FROM users u
LEFT JOIN users_arrivals po ON po.user_id = u.id
WHERE '2022-09-19' BETWEEN po.start_date AND po.end_date
OR '2022-09-25' BETWEEN po.start_date AND po.end_date;