On Nov 3, 2008, at 6:39 AM, Ingo Koch wrote: > Hi, > > I've got a question concerning a query with subselects. > I have a table with stores pairs of events. one of the events is > kind of a start event and the other one is a stop event. > Each event is stored in its own row. What I'm trying to achive is to > get a view which contains rows with the start event and the > corresponding stop event in one row. It works somehow, but only > somehow. :-( > > Here is some test data: > --------------------------------- > CREATE TABLE "TBOOKING" ( > "ID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > "EVENTTIMESTAMP" TIMESTAMP NOT NULL, > "EVENTTYPE" INTEGER NOT NULL, > "EMPLOYEE" INTEGER); > > INSERT INTO "TBOOKING" VALUES(42,'2008-09-22 09:19:35.000',3,NULL); > INSERT INTO "TBOOKING" VALUES(43,'2008-09-22 09:24:50.000',4,NULL); > INSERT INTO "TBOOKING" VALUES(44,'2008-09-22 10:43:03.000',3,NULL); > INSERT INTO "TBOOKING" VALUES(45,'2008-09-22 10:48:46.000',4,NULL); > INSERT INTO "TBOOKING" VALUES(46,'2008-09-22 11:56:56.000',3,NULL); > INSERT INTO "TBOOKING" VALUES(47,'2008-09-22 12:01:13.000',4,NULL); > INSERT INTO "TBOOKING" VALUES(48,'2008-09-22 14:23:05.000',3,NULL); > INSERT INTO "TBOOKING" VALUES(49,'2008-09-22 14:27:11.000',4,NULL); > > --------------------------------- > Here is the select for the view: > --------------------------------- > > SELECT > A.ID AS ID1, > A.EVENTTIMESTAMP AS TS1, > A.EVENTTYPE AS ET1, > (SELECT B.ID FROM TBOOKING AS B WHERE B.EVENTTYPE=4 AND B.ID>A.ID > LIMIT 1) AS ID2, > (SELECT B.EVENTTIMESTAMP FROM TBOOKING AS B WHERE B.EVENTTYPE=4 > AND B.ID>A.ID LIMIT 1) AS TS2, > (SELECT B.EVENTTYPE FROM TBOOKING AS B WHERE B.EVENTTYPE=4 AND > B.ID>A.ID LIMIT 1) AS ET2 > FROM TBOOKING AS A > WHERE A.EVENTTYPE=3; > > --------------------------------- > and here is the result: > --------------------------------- > > RecNo ID1 TS1 ET1 ID2 TS2 ET2 > ----- --- ------------------- --- --- ------------------- --- > 1 43 22.09.2008 09:19:35 3 43 22.09.2008 09:24:50 4 > 2 45 22.09.2008 10:43:03 3 45 22.09.2008 10:48:46 4 > 3 47 22.09.2008 11:56:56 3 47 22.09.2008 12:01:13 4 > 4 49 22.09.2008 14:23:05 3 49 22.09.2008 14:27:11 4 > > --------------------------------- > > Have a look at the column ID1. It should contain the values > 42,44,46, and 48. > > Is this a bug, or am I doing something wrong?
I think it's a bug. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
