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 (@endDate<check_in or @startDate>check_out) and hotel_id=@hotel_id", con);