Retreaving data based on whats not in a table

[bnbprojprobs]

Hi,

I have tables called room_status and room_info

room_status= room_id : date : status

room_info= room_id : type : rate

What I want to do is only make an entry in room_status when
a room is booked, as it is obviously available otherwise(duh) but
I don't know how to format a SELECT statement to bring back
rooms available on a specific date by only returning results from
the room_information table which do not have an entry in the room_status
table for a specific date, which is the case if I don't formally record that
a room is available.

thanks ppl

[raf]

Welcome here!

We don't have enough info to help you.
For instance, how are rooms reserved? Is this one record for each day?
Also, what mysql verson do you use? Can you use subquerys?
Do you have a rooms-table (i suppose so) and what's the design of that table?

The idea is to do a select for all rooms that are ented in rom_status for that date, and the use these in a second select with a 'Not In'-oprator.
That is, if yu create a new record for each days (so if the room is let for a week, then you create 7 records.

With mysql version >= 4.1, it would be as simple as

SELECT blabla FROM rooms WHERE room_id Not In (SELECT room_id FROM room_status WHERE `date`= '2004-01-01')

(i'm assuming here that you have a rooms-table with a column room_id as PK)

[bnbprojprobs]

thanks for the reply i sorted it fairly easily after using that
NOT IN operator.