selecting room type on room availability subquery

Question

table reserve

reserve_id
roominv_id
chckin
chckout
status (reserved,canceled,pending)

table roominventory

roominv_id
room_id
room_number
room_status(available , unavailable)

table room

room_id
room_type

I'm doing a room reservation system.

I already have the query for the date availability of the rooms :

SELECT r.roominv_id,r.room_id 
FROM roominventory r
WHERE r.roominv_id
NOT IN 
(SELECT b.roominv_id
FROM reserve b
WHERE NOT (b.chckout < '$chckin'
OR
b.chckin > '$chckout'))

My question is, where will I insert the condition in my query, where I can select a specific room type depends on the room availability.

I know it is like room.room_type LIKE '%$roomtype%' but I don't know which part of the query I will insert it.

score 2 · Answer 1 · answered Feb 02 '13 at 08:13

Here's an alternative approach using LEFT JOIN and checking for NULL:

SELECT 
  r.roominv_id, 
  r.room_id 
FROM roominventory AS r
    INNER JOIN room AS m ON m.room_id = r.room_id
    LEFT JOIN reserve res ON r.roominv_id = res.roominv_id AND
               NOT (b.chckout < '$chckin' OR b.chckin > '$chckout')
WHERE m.room_type LIKE '%$roomtype%' 
    AND res.roominv_id IS NULL

Good luck.

score 1 · Accepted Answer · answered Feb 02 '13 at 08:07

You can JOIN the table room like this:

SELECT 
  r.roominv_id, 
  r.room_id 
FROM roominventory AS r
INNER JOIN room AS m ON m.room_id = r.room_id
WHERE m.room_type LIKE '%$roomtype%'
  AND r.roominv_id NOT IN (SELECT b.roominv_id
                           FROM reserve b
                           WHERE NOT (b.chckout < '$chckin'
                                      OR 
                                      b.chckin > '$chckout'));

selecting room type on room availability subquery

2 Answers2

Linked