-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ingo Koch Sent: Monday, November 03, 2008 2:15 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Subselect question
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 ======================================================== Hello, Ingo, When you wrote: "... but I didn't want to keep track of an additional ID in the application but instead let the database do the work." I don't think I understand what logic the database is supposed to use to determine this. If you were talking to a database that was as smart as a human, how would you instruct it to choose the proper record without an eventId? Donald _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
