nwa
nwa

Reputation: 35

MySQL query table to get data between start and end date

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 need to query the table to retrieve all runIDs' that are not occupied between start and end time.

I know i can use the following query to retrieve runID's that are between two dates.

select runID 
from booking
where startDate >= '10/07/2015' 
and endDate <= 15/07/2015;

I'm mainly struggling with the architecture to retrieve unoccupied runs, so i can view available runs between two dates, maybe a boolean? Any suggestions.

Upvotes: 2

Views: 926

Answers (2)

bowlturner
bowlturner

Reputation: 2016

The below code will show you rooms that are not booked AT ALL during those dates

select run.runID 
from run  left join booking b on b.runID = run.runID
where startDate >= '10/07/2015' 
and endDate <= 15/07/2015
and b.runID is null;

Upvotes: 0

Mureinik
Mureinik

Reputation: 311123

Assuming you are looking for the runs that were not booked between these dates, you could use the not in operator with the given query as a subquery:

SELECT *
FROM   run
WHERE  run_id NOT IN (SELECT runID 
                      FROM   booking
                      WHERE  startDate >= '10/07/2015' AND
                             endDate <= '15/07/2015');

Upvotes: 1

Related Questions