我这样做：

I'd do that this way:

SELECT RM.room_no, RM.room_status, RM.housekeeping_status, CASE WHEN GR.roomno IS NULL THEN 'F' ELSE 'R' END AS FreeOrReserved
FROM room_master AS RM LEFT JOIN 
(
    SELECT *
    FROM guestreservation 
    WHERE arrivaldate = CAST(GETDATE() AS SMALLDATETIME)
) AS GR ON RM.room_no = GR.roomno

BTW：从来没有像这样使用串联字符串：

BTW: Never, ever use concatenated string like this:

strsql="select roomno from guestreservation where arrivaldate=cast('" & Format(currdate(), "dd-MMM-yyyy") & "' as smalldatetime)"

因为您为数据库公开 SQL Injection [ ^ ]。

更好地使用参数化查询 [ ^ ]（在此例如 - 你不需要）。



详情请见：

SQL连接的可视化表示 [ ^ ]

如何：执行参数化查询| Microsoft Docs [ ^ ]

because you expose you database for SQL Injection[^].
Better use parameterized queries[^] (which in this example - you don't need).

For further details, please see:
Visual Representation of SQL Joins[^]
How to: Perform Parameterized Queries | Microsoft Docs[^]