Reputation: 35
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
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
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