Csaba wrote: > Firstly, perhaps you should be linking the start and stop event across > a common id rather than relying on a start and stop appearing as > consecutive entries. Without knowing more about where your database > comes from it's hard to say. > > If you insist on keeping the current structure, here's a way to get > what you want (you'll have to set the columns you want to keep as > appropriate): > > SELECT t.*, u.* > FROM TBOOKING AS t LEFT JOIN TBOOKING as u > ON t.ID+1=u.ID > WHERE t.EVENTTYPE+1=u.EVENTTYPE
The problem with the join is, that although t.ID+1=u.ID is most often the case, it's not guaranteed that t.ID+1=u.ID. Users may undo the last booking (by deleting it from the database) which is why the subselects have u.ID>t.ID as part of the where clause. So the join, no matter how simple and elegant it would be, isn't a choice. > If, however, you to have a common Id, as mentioned above, for paired > event start and stop rows, call it EventId, then you could do: > SELECT t.*, u.* > FROM TBOOKING AS t LEFT JOIN TBOOKING as u > ON t.EventId=u.EventId > WHERE t.EVENTTYPE+1=u.EVENTTYPE I thought about that too, but I didn't want to keep track of an additional ID in the application but instead let the database do the work. Maybe an additional table for the current eventid and a on insert trigger could do the trick without changing the application logic. I'll think about it. Thanks for your answer. Ingo _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
