Harshit Harshit - 4 months ago 11
SQL Question

hotel booking SQL query not working

I have a two tables:

Room

id
category_id
name
description
image
price
arrival_date
depature_date
status


Booking

id
name
email
mobile
category_id
room_id
arrival
departure


Q1. How can I check if a room is available or not for customers?

Q2. I want to show the room list that is empty for user input date

I am using this query and it gives me
error #1241 - Operand should contain 1 column(s)


SELECT * FROM room WHERE status = '1' AND id NOT IN
(SELECT * FROM booking
WHERE arrival='$arrival'
OR ( arrival BETWEEN '$arrival' AND '$departure' ) )

SELECT * FROM room WHERE status = '1' AND id NOT IN
(SELECT * FROM booking WHERE arrival='2/08/2013'
OR ( arrival BETWEEN '2/08/2013' AND '22/08/2013' ))

Answer

In order to your questions:

Q1. how can i check room is available or not for customers

I suppose a room without booking is available for customers, so try this:

SELECT *
FROM room r
WHERE NOT EXISTS(
    SELECT 'booking'
    FROM booking b
    WHERE b.room_id = r.id
    AND  (b.arrival BETWEEN '$arrival' AND '$departure')
)

Q2. i want to show thous room list that is empty for user input date

The query is the same, if doesn't exist any booking in that interval the room is empty (so is available for booking)

Comments