All suggestions (with a bug-fix or two) gave the same results and ran in
acceptable time. Thanks to everyone for their help. I went with the
sub-SELECT solution. Not because it gave superior results but because I'm not
very familiar with sub-SELECT and it's good to have a working example in
Hi Simon,
I do this type of query all the time to avoid sub queries and aggregation.
This might be what you are looking for to satisfy the elegance criteria:
SELECT r.room_id,
b.date
FROM roomr
LEFT JOIN
booking b ON r.room_id = b.room_id
LEFT JOIN
booking b2 ON
On Thu, 4 Feb 2016 20:40:56 +
Simon Slavin wrote:
>
> On 4 Feb 2016, at 7:16pm, Luuk wrote:
>
> > Will this simple solution be too slow?:
> >
> > SELECT id, room, date, time
> > FROM rooms
> > LEFT JOIN bookngs ON rooms.id=bookings.room
> > GROUP BY rooms.id, bookings.date
> > HAVING
On 4 Feb 2016, at 7:16pm, Luuk wrote:
> Will this simple solution be too slow?:
>
> SELECT id, room, date, time
> FROM rooms
> LEFT JOIN bookngs ON rooms.id=bookings.room
> GROUP BY rooms.id, bookings.date
> HAVING bookings.date=MAX(bookings.date) OR bookings.date IS NULL
That's the solution
On 04-02-16 19:32, Simon Slavin 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
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
On 2/4/16, Simon Slavin wrote:
>
> 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
>
> I want to show an HTML table which lists some rooms (using WHERE and ORDER
> BY) and the latest time each
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
On Thu, Feb 4, 2016 at 12:32 PM, Simon Slavin 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
9 matches
Mail list logo