Question: Searching between two dates in mysql

Question

Searching between two dates in mysql

Answers 2
Added at 2016-12-30 07:12
Tags
Question

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.

Answers to

Searching between two dates in mysql

nr: #1 dodano: 2017-01-05 09:01
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.

nr: #2 dodano: 2017-01-05 11:01

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.

Source Show
◀ Wstecz