Might want to split bookings into a transaction table and a reservation table.
The reservation table would have one column for each room and one row for each calendar day (assuming this is a respectable joint with no hourly reservations!). Reservation table has primary key of date and room entries start out as NA. As rooms are booked NAs are replaced with IDs from booking-transaction table. Jim <https://www.avast.com/sig-email> This email has been sent from a virus-free computer protected by Avast. www.avast.com <https://www.avast.com/sig-email> <#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2> On Thu, Feb 4, 2016 at 1:32 PM, Simon Slavin <slavins at bigfraud.org> wrote: > Simplified explanation. Here's the setup: > > Two tables: > > rooms: each room has an id (think the standard SQLite rowid/id) and some > other columns > bookings: includes room id, date, time, and some other stuff > > Date/time is encoded as a long COLLATEable string. In other words sorting > a column by my date/time format will sort correctly into date order. > > Bookings may be made in reverse order. In other words, someone may book a > room for next month, and after that someone may book the same room for > tomorrow. > > System contains about 50 rooms and 5000 bookings. I can create whatever > indexes you want. > > Requirement: > > I want to show an HTML table which lists some rooms (using WHERE and ORDER > BY) and the latest time each of those rooms is booked for. At the moment > my code uses one SELECT to get room details and one SELECT for each room to > find the most recent booking. > > The obvious thing is to combine the two using JOIN. But the best JOIN I > can think of has to find the most recent booking using a clunky test for > the biggest date. And I can't figure out a good phrasing for a sub-select. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
