MySQL query between dates

Question

I have the following table, where bookings can be made

CREATE TABLE booking (
    bookingID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    customerID INT,
    runID INT,
    startDate DATETIME,
    endDate DATETIME,
    dateBookedOn TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (bookingID),
        INDEX idx_start (startDate),
        INDEX idx_end (endDate),
        FOREIGN KEY (runID) REFERENCES RUn(RunID),
        FOREIGN KEY (customerID) REFERENCES customer(CustomerID)
)

Then i have a run table

CREATE TABLE run
(
    RunID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    RunName VARCHAR(15),
    PricePerNight DECIMAL(3,2),
    primary key (RunID)
);

I am querying the run table to get all runID's that do not lie between two dates in the booking table, as follows.

SELECT *
FROM   run
WHERE  Runid NOT IN (SELECT RunID 
                  FROM   booking
                  WHERE CAST(startDate AS DATE)  >= '2015-07-19' AND
                         CAST(endDate AS DATE) <= '2015-07-25');

How would I change the query to select runID's that don't have ANY dates between them, if the dates overlap with query dates they are included in result set.

ie if a new booking had a startDate 2015-07-15 and a endDate 2015-07-21 then it will still show in the queries result set.

Norbert · Accepted Answer · 2015-07-10 20:22:05Z

1

Your query does what you want, but might be a bit slow: Avoid NOT IN if you can. This should do the same but is just faster:

SELECT a.*
FROM   run a
LEFT JOIN booking b ON a.runID=b.runID
AND startDate>= '2015-07-19' AND endDate<= '2015-07-25' 
WHERE b.runID IS NULL;

Also avoid casting your data from your database: If you want/have to cast anything, cast your variables: Casting your variable=1 value cast, casting the data from your database is N values cast, plus that indexes can not be used anymore.

answered Jul 10, 2015 at 20:22

Norbert

6,1043 gold badges19 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL query between dates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related