I have above mysql table with available dates and prices. Second table includes room details. How can I join two tables to get available rooms between two dates and not get duplicate content.
3条回答 默认 最新
- duanjiushu5063 2010-10-02 14:56关注
This is hard to come up with a complete answer for you here, as you are only showing us the table which contains the bookings - we cannot know what range of rooms are available.
SQL which returns the room_id's for rooms which are booked for at least part of the selected period could be:
SELECT `room_id` , COUNT(*) FROM `bookings` WHERE `dt` BETWEEN "[start date]" AND "[end date]" GROUP BY `room_id`;
If you had a table of rooms (rather than bookings), it would be possible for you to return a list of any rooms not booked during that period with:
SELECT `id` FROM `rooms` WHERE `id` NOT IN ( SELECT DISTINCT( `room_id` ) FROM `bookings` WHERE `dt` BETWEEN "[start date]" AND "[end date]" );
AMENDMENT
Based on the feedback by OP, the assumptions are now:
- The table contains details of rooms which are available for a period starting on the date in column
dt
and ending the following day (ie hotel rooms) - The query should return any rooms which are available for the entirity of the period entered (so only rooms which are available from DAY A to DAY B will be returned.
As such, the amended code is:
SELECT room_id FROM available_rooms WHERE dt BETWEEN "[start date]" AND DATE_SUB("[end date]",INTERVAL 1 DAY) GROUP BY room_id HAVING COUNT(*)=ABS(DATEDIFF("[start date]","[end date]"));
本回答被题主选为最佳回答 , 对您是否有帮助呢?解决 无用评论 打赏 举报 - The table contains details of rooms which are available for a period starting on the date in column
悬赏问题
- ¥15 delphi webbrowser组件网页下拉菜单自动选择问题
- ¥15 wpf界面一直接收PLC给过来的信号,导致UI界面操作起来会卡顿
- ¥15 init i2c:2 freq:100000[MAIXPY]: find ov2640[MAIXPY]: find ov sensor是main文件哪里有问题吗
- ¥15 运动想象脑电信号数据集.vhdr
- ¥15 三因素重复测量数据R语句编写,不存在交互作用
- ¥15 微信会员卡等级和折扣规则
- ¥15 微信公众平台自制会员卡可以通过收款码收款码收款进行自动积分吗
- ¥15 随身WiFi网络灯亮但是没有网络,如何解决?
- ¥15 gdf格式的脑电数据如何处理matlab
- ¥20 重新写的代码替换了之后运行hbuliderx就这样了