I'm making a hotel reservation system.
My booking page has a Jcalendar to pick a date and "Show available Room" button.
When date is picked and button pressed, the table should show the available room.
The code for the button is
SELECT * FROM RoomInfo WHERE (
number NOT IN (
SELECT roomNo FROM Booked
) AND "+reportDate+" NOT IN (
SELECT date FROM Booked
number is the room number in
RoomInfo, which is
reportDate is the TEXT string of picked date in yyyyMMdd format and
Booked is also in yyyyMMdd format.
As an example
row roomNo date
1 101 20160110
When date 20160110 picked and the expected output is room 201 and 301, but nothing is returned.
When date 20160111 picked, the expected output is room 101, 201 and 301, but the returned is 201 and 301.
What's wrong with my query? I'm new in SQLite, feel free to teach me.
Your query is incorrect. In your query the first condition will automatically exclude all rooms since you are selecting everything from the
Booked table. You only want to select
Booked rooms for the date you specified so move the date restriction into the first suquery.
Try the following:
"SELECT * FROM RoomInfo WHERE number NOT IN ( SELECT roomNo FROM Booked WHERE date = "+reportDate+" )"