1

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?"

Community
  • 1
  • 1
Mickey Cheong
  • 2,980
  • 6
  • 38
  • 50
  • 1
    Too much work reading that other stuff. Don't be lazy and post the query that you are having troubles with and the table schema nicely formatted on this question. – Icarus Oct 13 '11 at 00:32
  • 2
    Show us the query you have so far... – D'Arcy Rittich Oct 13 '11 at 00:40
  • So what is your parameter? The date of the Sunday that begins the week? The week number for the year? Elaborate, pls. – bug11 Oct 13 '11 at 01:03

1 Answers1

1

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
ivanatpr
  • 1,862
  • 14
  • 18