Mysql Left Join with Group By and Between

Question

I'm having trouble creating a select query that will retrieve all user IDs that are not in the games, and availability between dates.

Example. Retrieve all users who are not between dates 2017-08-10and 2017-08-12.

Get [3 , 4, 5]

Users

| Id | Name     |
| 1  | Jonh     |
| 2  | Mark     |
| 3  | Caroline |
| 4  | David    |
| 5  | George   |

Games

| Id | User_Id  | Start_Date | End_Date   |
| 1  | 1        | 2017-06-01 | 2017-06-01 |
| 2  | 1        | 2017-08-12 | 2017-08-13 |
| 3  | 4        | 2017-08-13 | 2017-08-14 |

Availability

| Id | User_Id | Start_Date | End_Date   |
| 1  | 1       | 2017-05-01 | 2017-05-25 |
| 1  | 2       | 2017-08-10 | 2017-08-17 |
| 1  | 3       | 2017-06-20 | 2017-07-10 |

I am using Laravel 5.4, but I will be glad if the answer is Raw or Eloquent.

Answer 1

In SQL, you can use NOT EXISTS:

select *
from users u
where not exists (
        select 1
        from games g
        where u.id = g.user_id
            and (
                g.start_date between '2017-08-10' and '2017-08-12'
                or g.end_date between '2017-08-10' and '2017-08-12'
                )
        )
    and not exists (
        select 1
        from Availability a
        where u.id = a.user_id
            and (
                a.start_date between '2017-08-10' and '2017-08-12'
                or a.end_date between '2017-08-10' and '2017-08-12'
                )
        );

Demo

Another way to use LEFT JOIN:

select distinct u.*
from t_users u
left join games g on u.id = g.user_id
    and (g.start_date between '2017-08-10' and '2017-08-12'
        or g.end_date between '2017-08-10' and '2017-08-12')
left join availability a on u.id = a.user_id
    and (a.start_date between '2017-08-10' and '2017-08-12'
        or a.end_date between '2017-08-10' and '2017-08-12')
where g.user_id is null and a.user_id is null;

Demo

Answer 2

- MMDDYYYY DDMMYYYY, UNIXTIME,

SELECT * 
FROM Users
LEFT JOIN Games ON Users.Id = Games.Id_User
LEFT JOIN Availability ON Users.Id = Availability.Id_User
WHERE ISNULL(Games.Id) AND ((Start_Date > EndDateUnixtime OR End_Date < StartDateUnixtime)  OR ISNULL(Start_Date) OR ISNULL(End_Date))

DMMYYYY unixtime, .

unix_timestamp(str_to_date('30/05/2011','%d/%m/%Y'))