Q:

MySQL - view select rooms not in booking range

I'm using a select query within a view to find when a room is available, a booking is identified with a reference and a start/end date.
view:
USE CATTERY2;
CREATE VIEW RoomAvalability
AS
SELECT ROOMINFO.ID,# ROOMINFO.`Type`, ROOMINFO.Max,
    #GROUP_CONCAT(DISTINCT BOOKROOM.Ref) as Bookings,
    GROUP_CONCAT(DISTINCT BOOK.Ref) as BookRef, ##
    GROUP_CONCAT(DISTINCT BOOK.Start_Date) as StartDate,
    GROUP_CONCAT(DISTINCT BOOK.End_Date) as EndDate
FROM ROOMINFO
LEFT JOIN BOOKROOM ON ROOMINFO.ID = BOOKROOM.ID
INNER JOIN BOOK ON ROOMINFO.ID AND BOOK.Ref = BOOKROOM.Ref ##
GROUP BY ROOMINFO.ID, ROOMINFO.`Type`, ROOMINFO.Max;

A room can have multiple bookings (which don't overlap) within the current bookings logged.
The problem I'm currently having is the select query to specify a date range, I cannot wrap my head around it.
I want to get returned from the query the list of rooms which don't have a booking for the specified date range.
I'm currently trying the following 3 selects for testing:
#1
SELECT * FROM RoomAvalability;
#2
SELECT ID, `Type`, Max FROM RoomAvalability WHERE NOT StartDate <= '2019-11-22' AND EndDate <= '2020-11-28';
#3
SELECT ID, `Type`, Max FROM RoomAvalability WHERE StartDate <= '2019-11-22' AND EndDate <= '2020-11-28';

1 returns all rooms
2 also returns all rooms
3 returns no rooms 
1 & 2 results:
ID, type max
1   2   4
2   2   4
3   2   4
4   1   2
5   1   2
6   1   2
7   1   2
8   1   2
9   1   2
10  1   2

Shouldn't number 2 be returning the 8 rooms instead of 10, as it's the negative of number 3?
number 3 provides no results meaning there isn't a booking within this range, but it should provide 2 results?
The other thing I am thinking: should I be starting from the booking and link the room instead?
structure and values
CREATE SCHEMA CATTERY2;
USE CATTERY2;
#BOOK
CREATE TABLE BOOK(
           Ref INT NOT NULL AUTO_INCREMENT,                    
           Start_Date DATE NOT NULL,
           End_Date DATE NOT NULL,
           PRIMARY KEY(Ref));
#ROOM
CREATE TABLE ROOMINFO(
          ID INT NOT NULL AUTO_INCREMENT,                       
          `Type` VARCHAR(10) NOT NULL,
          Max TINYINT NOT NULL,
          PRIMARY KEY(ID));
#BOOKROOM
CREATE TABLE BOOKROOM(
           Ref INT NOT NULL,
           ID INT NOT NULL,
           FOREIGN KEY (Ref) REFERENCES BOOK(Ref),
           FOREIGN KEY (ID) REFERENCES ROOMINFO(ID));

INSERT INTO BOOK(Start_Date, End_Date) VALUES   
("2019-11-22", "2019-11-25"),
("2019-11-24", "2019-11-28"),
("2019-12-01", "2019-12-02"),
("2019-12-01", "2019-12-06"),
("2019-12-02", "2019-12-03"),
("2019-12-04", "2019-12-10"),
("2019-12-04", "2019-12-10"),
("2019-12-05", "2019-12-13"),
("2019-12-16", "2019-12-19"),
("2019-12-26", "2019-12-28"),
("2019-12-26", "2020-01-01"),
("2019-12-28", "2020-01-02"),
("2019-12-31", "2020-01-05"),
("2020-01-03", "2020-01-08"),
("2020-01-05", "2020-01-11"),
("2020-01-06", "2020-01-09"),
("2020-01-06", "2020-01-11"),
("2020-01-08", "2020-01-18"),
("2020-01-11", "2020-01-15"),
("2020-01-15", "2020-01-17"),
("2020-01-15", "2020-01-18");

INSERT INTO ROOMINFO (ID, `Type`,Max) VALUES
(1, "Family", 4),
(2, "Family", 4),
(3, "Family", 4),
(4, "Dual", 2),
(5, "Dual", 2),
(6, "Dual", 2),
(7, "Dual", 2),
(8, "Dual", 2),
(9, "Dual", 2),
(10, "Dual", 2);

INSERT INTO BOOKROOM( Ref, ID ) VALUES
(1, 4),
(2, 3),
(3, 4),
(4, 5),
(5, 6),
(6, 7),
(7, 3),
(8, 2),
(9, 1),
(10, 8),
(11, 3),
(12, 9),
(13, 2),
(14, 10),
(15, 4),
(16, 5),
(17, 6),
(18, 7),
(19, 2),
(20, 1),
(21, 10);

A:

Try this:
SELECT r.id
FROM ROOMINFO AS r
WHERE NOT EXISTS (SELECT 1 
                    FROM BOOKROOM br
                    JOIN BOOK b ON br.ref = b.ref
                    WHERE r.id = br.id
                        AND ('2019-11-22' BETWEEN b.Start_Date AND b.End_Date
                            OR '2019-11-28' BETWEEN b.Start_Date AND b.End_Date
                            OR b.Start_Date BETWEEN '2019-11-22' AND '2019-11-28'
                            OR b.End_Date BETWEEN '2019-11-22' AND '2019-11-28'
                            )
                  );

Or if you add room id to BOOK table you could use such query:
SELECT r.id
FROM ROOMINFO AS r
WHERE NOT EXISTS (SELECT 1 
                    FROM BOOK 
                    WHERE r.id = id
                        AND ('2019-11-22' BETWEEN Start_Date AND End_Date
                            OR '2019-11-28' BETWEEN Start_Date AND End_Date
                            OR Start_Date BETWEEN '2019-11-22' AND '2019-11-28'
                            OR End_Date BETWEEN '2019-11-22' AND '2019-11-28'
                            )
                );

In this case you can create supporting index ON BOOK(id, Start_Date, End_Date)

