Sql query for hotel reservation

Shibly Sadik

9y4.1kSQL

User book hotel room by selecting hotel id and by giving a check in , check out date.Now i want to retrieve the room number(room_no) from tblRoom those are not in tblReservation (I mean the rooms that are not booked yet), and also the room number (room_no) those are in tblReservation but not between the check in, check out date.

The below code allow to get the room_id . But but i need the room_no. Cancan you help me?

SqlCommand cmd = new SqlCommand(@"SELECT room_id FROM tblRoom WHERE (hotel_id=@hotel_id AND

room_id NOT IN (SELECT room_id FROM tblReservation)) union select room_id from tblReservation

where (@endDatecheck_out) and hotel_id=@hotel_id", con);

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nilesh Shah9y ago
Accepted answer
I don't know what's stopping you to select the room no along with the room id as both belong to same table and you are not using any group by clause.

I would also suggest some chages in the where condition which seems not producing desired results
SqlCommand cmd = new SqlCommand(@"SELECT room_id,room_no FROM tblRoom WHERE (hotel_id=@hotel_id AND
room_id NOT IN (select room_id from tblReservation
where @endDatecheck_out) and hotel_id=@hotel_id", con);
Shibly Sadik9y ago
Thanks for your valuable answer.It worked as i wanted. one more help please,if i delete the hotel_id column from tblRoom then how can i get the room_no like before?