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
