SQL find two consecutive days in a reservation system
Extending on this question: hotel reservation system SQL query: identify when one specific room is available
Using the schema listed in the question above, how can I have a query that says "开发者_如何学运维Find me a room for 2 consecutive days thats available in this week?"
Just join to the availability table twice
SELECT rooms.* FROM rooms, availability as a1, availability as a2
WHERE rooms.id = 123
AND a1.room_id = rooms.id
AND a2.room_id= rooms.id
AND a1.date_occupied + 1 = a2.date_occupied
or, if we're not into writing SQL like its 1985:
SELECT rooms.* FROM rooms
JOIN availability a1 on a1.room_id = rooms.id
Join availability a2 on a2.room_id = rooms.id AND a1.date_occupied + 1 = a2.date_occupied
WHERE rooms.id = 123
精彩评论