selecting room type on room availability subquery

1
votes

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.

2
sql

2 Answers

2
votes

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.

1
votes

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'));