Re: [sqlite] Subselect question
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.IDA.ID LIMIT 1) AS ID2, (SELECT B.EVENTTIMESTAMP FROM TBOOKING AS B WHERE B.EVENTTYPE=4 AND B.IDA.ID LIMIT 1) AS TS2, (SELECT B.EVENTTYPE FROM TBOOKING AS B WHERE B.EVENTTYPE=4 AND B.IDA.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. http://www.sqlite.org/cvstrac/chngview?cn=5855 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subselect question
-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.IDt.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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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.IDt.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
Re: [sqlite] Subselect question
Griggs, Donald wrote: 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? Well, I don't need an eventId in the application. All I need is the information about the startevent and the corresponding stopevent. SQLite *can* collect the necessary information (see the select in my initial posting) for *my usecase*. The misbehaviour that I reported is confirmed as a bug and corrected by Dan. So the database hasn't to be as smart as a human, it only has to be as smart as SQLite, and I try to instruct it with my limited SQL knowledge. ;-) I, personally, try to reduce application logic to simple insert, select and delete statements. Anything else related to the data stored in the database and the relations between the tables should be handled by the database itself (with the help of the application programmer of course by means of triggers, database procedures and functions). Normally, if handled internally, the database is much faster to do the adequate things than an application through an interface can do. And as a result of that this is the single point on my wish list for SQLite: Stored procedures and functions with support for variables. Ingo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subselect question
Griggs, Donald [EMAIL PROTECTED] wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ingo Koch Sent: Monday, November 03, 2008 2:15 PM 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.IDt.ID as part of the where clause. So the join, no matter how simple and elegant it would be, isn't a choice. How about this: select A.*, B.* from TBOOKING A, TBOOKING B where A.EVENTTYPE = 3 and B.ID = ( select min(C.ID) from TBOOKING C where C.EVENTTYPE = 4 and C.ID A.ID ); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subselect question
Igor Tandetnik wrote: How about this: select A.*, B.* from TBOOKING A, TBOOKING B where A.EVENTTYPE = 3 and B.ID = ( select min(C.ID) from TBOOKING C where C.EVENTTYPE = 4 and C.ID A.ID ); Igor Tandetnik Igor, you are my hero ;-) I've tried a subselect in the join but I've missed the (somehow obvious) min(ID) part. Ingo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Subselect question
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.IDA.ID LIMIT 1) AS ID2, (SELECT B.EVENTTIMESTAMP FROM TBOOKING AS B WHERE B.EVENTTYPE=4 AND B.IDA.ID LIMIT 1) AS TS2, (SELECT B.EVENTTYPE FROM TBOOKING AS B WHERE B.EVENTTYPE=4 AND B.IDA.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
Re: [sqlite] Subselect question
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 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 Csaba Gabor from Vienna On Mon, Nov 3, 2008 at 12:39 AM, Ingo Koch [EMAIL PROTECTED] 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.IDA.ID LIMIT 1) AS ID2, (SELECT B.EVENTTIMESTAMP FROM TBOOKING AS B WHERE B.EVENTTYPE=4 AND B.IDA.ID LIMIT 1) AS TS2, (SELECT B.EVENTTYPE FROM TBOOKING AS B WHERE B.EVENTTYPE=4 AND B.IDA.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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subselect question
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.IDA.ID LIMIT 1) AS ID2, (SELECT B.EVENTTIMESTAMP FROM TBOOKING AS B WHERE B.EVENTTYPE=4 AND B.IDA.ID LIMIT 1) AS TS2, (SELECT B.EVENTTYPE FROM TBOOKING AS B WHERE B.EVENTTYPE=4 AND B.IDA.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
[sqlite] Subselect question
Hey all, Quick (and hopefully simple) question regarding subselects in a where clause. Does sqlite cache the values of a subselect so it doesn't have to run the query each time it evaluates a row? Example: SELECT * FROM foo WHERE id NOT IN (SELECT id FROM bar) Cheers, Shawn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subselect question
On Jul 9, 2008, at 3:23 PM, Shawn Wilsher wrote: Hey all, Quick (and hopefully simple) question regarding subselects in a where clause. Does sqlite cache the values of a subselect so it doesn't have to run the query each time it evaluates a row? Example: SELECT * FROM foo WHERE id NOT IN (SELECT id FROM bar) Yes it does. Assuming it is possible. If the subquery depends on the outer query that is not possible. Example: SELECT * FROM foo WHERE id NOT IN (SELECT id FROM bar WHERE xfoo.y) So in your original query, the subquery runs once and the results are cached. In the second query, the subquery runs once for each row in the result set. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
