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? Thanks for your answers. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users