I have a database which looks like this:
<https://i.imgur.com/dwaqUF2.png>

I want to select all rooms which don't have any reservation for a given time period.

Here is what I tried:

```
SELECT idRoom, type, beds FROM Room r
INNER JOIN Reservation_has_Room has on r.idRoom = has.Room_idRoom
INNER JOIN Rezervation re on has.Reservation_has_Room = re.idReservation
 WHERE (re.checkIn<'2019-06-04'
 AND re.checkOut<'2019-06-01') 
 or
(re.checkIn>'2019-06-04'
 AND re.CheckOut>'2019-06-01');

```

But this script returns a room every time it finds a reservation which is not overlapping the date.

edit:
=====

I think I might be misunderstood.
This is the case:
In Rooms i have this records:

```
 idRoom, type, beds
'1', 'Standard', '1'
'2', 'Standard', '1'

```

Reservation:
------------

```
 idReservation, checkIn, checkOut
'1', '2019-05-22', '2019-06-03'
'2', '2021-05-22', '2021-06-03'
'3', '2022-05-22', '2022-06-03'

```

Reservation\_has\_Room
----------------------

```
 Reservation_idReservation, Room_idRoom
'1', '1'
'2', '1'
'3', '1'

```

So as you can see for room nr 1 I have 3 reservation. One is overlapping and rest are not. In this case, the script from @AlexYes returns this:

```
 idRoom, type, beds
'1', 'Standard', '1'
'1', 'Standard', '1'
'2', 'Standard', '1'

```

So it returns room number one, even two times.
My expected result is :

```
 idRoom, type, beds
'2', 'Standard', '1'

```

So only room nr 2 is available.