CODEWITHSUNDEEP
phpmysql
user7136014
user7136014

Reputation:

Searching between two dates in mysql

I have two tables and this table name is "rooms" Table Rooms and other one is "bookings" Table Bookings

I joined two tables now, I want values when i will search between book_form = "2016-12-30" and book_to = "2016-12-31" it will be return true because this two dates does not exists in the "bookings" table, and when search between book_form = "2016-12-30" and book_to = "2017-01-05" or book_form = "2017-01-03" and book_to = "2017-01-15" it will be return false because this date exists in bookings table.

This is my query. 

select * from rooms join room_book on rooms.room_id = room_book.room_id where status = 'available' and room_book.book_from NOT BETWEEN '2016-12-30' AND room_book.book_to NOT BETWEEN '2016-12-31'

NOTE: Sorry actually the column book_from date is 2017-01-01 in the bookings table.

Upvotes: 4

Views: 903

Answers (2)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

A simple SQL query should return only those rooms without a booking that includes the supplied date:

SELECT *
FROM rooms
LEFT JOIN room_book
    ON room_book.room_id = rooms.room_id
    AND 'search_date' BETWEEN room_book.book_from AND room_book.book_to
WHERE rooms.room_id IS NULL
AND rooms.status = 'available'

Substitute the date you are searching for search_date above.

By using a LEFT JOIN, you will get all of the records in rooms. The IS NULL test in the where clause eliminates those rows that don't have a matching row in room_book.

Upvotes: 0

Naveed Ramzan
Naveed Ramzan

Reputation: 3593

select * 
from rooms 
join room_book on rooms.room_id = room_book.room_id 
where status = 'available' 
and room_book.book_from >= '2016-12-30' 
AND room_book.book_to <= '2016-12-31'

Try like this.

Upvotes: 1

Related Questions