急求难题订房查询房间SQL！！！

Question

在线订房系统，如何实现查询当前是否有所要查询的房间，
表结构如下：
--房间表room：roomId,groupId,roomName
--房间分组表group：groupId,groupName
--订单明细表orderItem：orderItemId,roomId,StartDate,EndDate

如何实现在页面输入起始时间：[img]http://dl.iteye.com/upload/attachment/434645/4111744f-605e-3c1f-a57b-a8deabd2d558.gif[/img]如startDate:2010-3-1,endDate:2010-3-5
查询出空房信息，显示在页面上，如：标准客房：已满；商务房：可预订

[img]http://dl.iteye.com/upload/attachment/434648/cdd8ca95-b203-3c14-be0c-7c0bc014fc9c.gif[/img]

Answer 1

[code="sql"]select groupName from group where groupid in (select groupId from room where roomid not in (select roomid from orderItem where (startTime > '2010-3-1' and startTime < '2010-3-5' ) || (endTime > '2010-3-1' and endTime < '2010-3-5' )));[/code]