Hotel Booking application rooms avaiability

Question

I'm working on a hotel booking app, where you can select a date and a room type and the application shows the available rooms. I'm using the Java Spring framework to do this.

This are the tables that i think matter to this query:

CREATE TABLE IF NOT EXISTS `booking` (
  `id` bigint(20) NOT NULL,
  `aproved` bit(1) NOT NULL,
  `begin_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `room_id` bigint(20) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `room` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `room_type_id` bigint(20) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=161 DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `room_type` (
  `id` bigint(20) NOT NULL,
  `number_of_rooms` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  `type` varchar(255) DEFAULT NULL,
  `hotel_id` bigint(20) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

I'm having dificulties making that query...

I made this one, but it's not a good idea to join the rooms with boookings because that will only select the rooms when there are bookings...

SELECT * 
FROM room r join booking b on b.room_id = r.id join room_type rt on r.room_type_id = rt.id 
WHERE not ((b.begin_date >= :initDate And b.begin_date <= :endDate) or (b.begin_date >= :initDate And b.end_date <= :endDate) or (b.begin_date <= :initDate and b.end_date >= :endDate) and b.aproved = true and rt.id = :roomType)

Any ideas?

MarianP MarianP · Accepted Answer · 2015-11-20T16:16:50

select * from rooms r
where r.room_type_id = :desiredRoomType
and not exists (
 select * from bookings b
 where begin_date >= :desiredDate
 and end_date <= :desiredDate
)

I am not sure, why begin_date/end_date might be null in your case, if they really can be, the query should reflect that.

Hotel Booking application rooms avaiability

1 Answers