How to check available hotel room's C# and SQL

Question

I am making a hotel reservation system in windows form.User book hotel room by selecting hotel id and by giving a check in ,check out date.Now i want to retreive the room id's from tblRoom those are not in tblReservation(i mean the rooms that are not booked yet) and also the room id's those are in tblReservation but not between the check in,check out date.can you help me?

Satyaprakash Samantaray · Answer

using "Where not in"

Ramesh Barik · Answer

Hi Shibly,      SELECT room_no FROM TblRoom LEFT JOIN tblReservation ON TblRoom.room_id = tblReservation.room_id WHERE check_out AND tblReservation.room_id IS NULL;   Try this query.

Piyush Pansuriya · Answer

Hello,    Please try this :   SELECT room_no FROM TblRoom LEFT OUTER JOIN tblReservation ON TblRoom.room_id=tblReservation.room_id WHERE check_in>GETDATE() OR check_out OR tblReservation.room_id IS NULL

Ramesh Palanivel · Answer

Hi Shibly,   Yes. I understand your requirement.   It simple.. you can do this by using "Where not in"   Ex : "select roomid from tlRoom where not in(select room_id in tblreservation)"   Try thie method, it may help you..   Another way is looping the tblreservation room id value and save the value in string and pass it in your query,   string value="1,2,,3" //construct your room id   write your query like "select roomid from tblroom where not in('"+value+"')"

Satyaprakash Samantaray · Answer

SELECT room_no 'Room No'
FROM TblRoom as TR
LEFT JOIN tblReservation as TRE
ON TR.room_id = TRE.room_id WHERE check_out AND TRE.room_id IS NULL