Select available rooms between two dates

Question

I have above mysql table with available dates and prices. Second table includes room details. How can I join two tables to get available rooms between two dates and not get duplicate content.

Answer 1

Assuming the table you showed us is called rooms_dates with two other tables rooms and room_details:

select room.id, room_details.xxxxxxx from rooms
inner join room_details on rooms.id = room_details.room_id
where rooms.id in
  (
    select distinct room_id from rooms_dates
    where dt >= 'xxxx-xx-xx' and dt <= 'yyyy-yy-yy'
  );

Answer 2

SELECT available.* , rooms.* FROM available, rooms 
WHERE available.room_id = rooms.room_id AND 
available.dt BETWEEN '2005-01-01' AND '2005-12-31'

Answer 3

This is hard to come up with a complete answer for you here, as you are only showing us the table which contains the bookings - we cannot know what range of rooms are available.

SQL which returns the room_id's for rooms which are booked for at least part of the selected period could be:

SELECT `room_id` , COUNT(*)
FROM `bookings`
WHERE `dt` BETWEEN "[start date]" AND "[end date]"
GROUP BY `room_id`;

If you had a table of rooms (rather than bookings), it would be possible for you to return a list of any rooms not booked during that period with:

SELECT `id`
FROM `rooms`
WHERE `id` NOT IN (
  SELECT DISTINCT( `room_id` )
  FROM `bookings`
  WHERE `dt` BETWEEN "[start date]" AND "[end date]"
);

AMENDMENT

Based on the feedback by OP, the assumptions are now:

• The table contains details of rooms which are available for a period starting on the date in column dt and ending the following day (ie hotel rooms)
• The query should return any rooms which are available for the entirity of the period entered (so only rooms which are available from DAY A to DAY B will be returned.

As such, the amended code is:

SELECT room_id
FROM available_rooms
WHERE dt BETWEEN "[start date]" AND DATE_SUB("[end date]",INTERVAL 1 DAY)
GROUP BY room_id
HAVING COUNT(*)=ABS(DATEDIFF("[start date]","[end date]"));