问题描述:

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 roomNo in Booked.

reportDate is the TEXT string of picked date in yyyyMMdd format and date in Booked is also in yyyyMMdd format.

As an example

RoomInfo db

-------------

row number

1 101

2 201

3 301

Booked db

-----------

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.

Thank you.

网友答案:

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+"
    )"
相关阅读:
Top