sql query to list vacant apartments

[zspoja]

I don't have any proper experience with MySQL but need to make a query from python to a database that would return a list of vacant apartments.

So, I know the number_of_beds, start_date and end_date and need to get a list of available apartments.

The two MySQl tables that I can query basically are:

APARTMENTS: (all available apartments)
apartment_id (key)
apartment_number_of_beds

BOOKINGS:
booking_apartment_id (key)
booking_start_date (key)
booking_end_date

Apartments are available up to and on start_date and from and including end_date.

Appreciate any help that would save me from dwelving into SQL queries

[hericles]

You need to make sure neither the selected start or end date of tenancy is the bookings table to ensure the apartment is free for that period. Assuming you have two inputs for the start and end dates this should work:

SELECT apartment_id FROM apartments AS a JOIN bookings AS b WHERE (startDate < b.booking_start_date AND endDate < b.booking_end_date) OR (startDate > booking_start_date AND endDate > b.booking_end_date)

I haven't tested this and it could be improved (it is basic in its business logic). For example, the startDate can be < (booking_start_date - 1) to ensure that it is available for at least 1 night.

[zspoja]

Thanks, for the help. We finally did the following:

SELECT * FROM apartments WHERE id NOT IN
  (SELECT DISTINCT apartments FROM reservations
     WHERE datefrom = $from_date
     OR datefrom > $from_date AND datefrom < $to_date
     OR datefrom < $from_date AND dateto > $from_date
     )