Hi Friends

This is saneed. I have a a problem while selecting specific data from a table The structure of my table is given below

#tableroom

room_id | room_no | room_type| ratepernight | maxpersons
1 101 d 1700.00 2
2 102 d 1700.00 2
3 103 d 1700.00 2
4 104 d 1700.00 2
5 105 d 1700.00 2
6 106 d 1700.00 2
7 107 d 1700.00 2
8 108 d 1700.00 2
9 109 d 1700.00 2
10 110 d 1700.00 2
11 111 d 1700.00 2
12 112 d 1700.00 2
13 113 s 2000.00 2
14 114 s 2000.00 2
15 115 s 2000.00 2

where room_id is the primary key

here my problem is i want to create a query for search availability of rooms as it is available in every hotel reservation system.Below is the structure of the reservation table. Please suggest me if anything more should be added in the table so as to make the search task easier.This is only for slef inplementation purpose as i am beginner...

Hope i could find a good response from everyone present here...

In tablereservation id is the primary key and total_cost is the total cost of the room for desired no. of days for eg if the room_id = 1 is booked for two nights then the total cost would be 1700x2 , checkIn states that whether the guest have checkin in the room or not Y denotes Yes and N Denotes N , checkOut states that whether the guest have checked out or left the room Y denotes Yes and N Denotes No. Status is used to check the current status of the room whether it is occupied or not.

#tablereservation

id | guest_name | date_of_reservation | room_id | arrival_date | departure_date | total_cost | checkIn | checkOut | status
1 saneed 17/07/2011 1 18/08/2011 20/08/2011 3400.00 Y N O
2 sayeed 16/08/2011 2 20/08/2011 22/08/2011 10200.00 N N V
3 saneed 16/08/2011 3 20/09/2011 22/09/2011 10200.00 N N V