Sql query for hotel reservation

Question

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);