1

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);

7
  • Hi and welcome to the forum! Could you please provide your table structures as DDL (CREATE TABLE foo (..)) and your data as DML (INSERT INTO foo VALUES (...)). Help us to help you!
    – Vérace
    Apr 15 '20 at 22:49
  • Also, you need to provide more data!
    – Vérace
    Apr 15 '20 at 22:57
  • I doubt this view is useful for your purpose. StartDate column will contain string with a list of dates and you need to parse this string to find required info. Also your database schema doesn't look optimal. If you want to avoid massive joins you need to have room id with booking dates in one table. What version of MySQL do you use? Apr 16 '20 at 10:34
  • What do you mean by but the create tables are python implementation?
    – Vérace
    Apr 16 '20 at 10:54
  • I'm sorry - but I'm still confused! There's no data! Please give us table structure(s) (present now), data, the queries you ran against that data, the results (clearly separated per query). Finally, there must be any number of F/LOSS offerings in this area - you could download a few of those and see how they organise their tables - that would be a good place to start. This might be of help?
    – Vérace
    Apr 16 '20 at 11:00
0

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)

1
  • @throwaway56786897 Sorry, I fixed conditions ) Apr 16 '20 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.