Re: [sqlite] Simple Select from IN - from a newbie.
CTEs seems like another magic ball. I just read up a little bit about them. The bag of tricks is getting more colorful. I would love to use this new feature, sadly the version of Sqlite is 3.7.7.1, you mentioned views, is this something that I can use for the version of Sqlite that I'm on. Many Thanks. On 5/23/14, RSmith [via SQLite] wrote: > > > > On 2014/05/22 17:25, Humblebee wrote: >> Oops back sooner than I thought. >> >> I would like to order the grouping of the Persons in a particular >> order. So the sequence of the result is based on the TeamPersonTable's >> order field. > > First a word of warning - Please do not use column names that are the same > as SQL keywords, such as "Order"... I took the liberty of > renaming the new column to "orderId" because of this. > > Next, introducing a great new feature of SQLite (and most other SQL Engines) > called CTE or Common Table Expression, which lets you > define one kind of table and then extract a query from it, all in the same > query. (This can also be achieved with Views if the SQL > Engine doesn't support CTE, such as older SQLites, but this is usually a > 2-step process which may or may not be more useful > depending if you use the same base query for other purposes too). > > Here it is simply used to make up a sorted table, and then from it do the > grouping and concat that is needed. > > Same set of Queries as before, but with ordering added - which again is made > possible by that 1NF layout. > > Per Team Query: > > WITH WTP(teamId,personIDs,personNames) AS ( > SELECT TP.teamId, P.id, P.name > FROM TeamPersonTable TP > LEFT JOIN PersonTable P ON P.id=TP.personId > WHERE TP.teamid=1 > ORDER BY TP.orderId > ) > SELECT group_concat(personIDs,','), group_concat(personNames,',') > FROM WTP > GROUP BY WTP.teamId; > > > Per person Query: > > WITH WTP(personId,teamIDs,teamNames) AS ( > SELECT P.id, T.id, T.name > FROM TeamPersonTable TP > LEFT JOIN TeamTable T ON T.id=TP.teamId > LEFT JOIN PersonTable P ON P.id=TP.personId > WHERE P.id=5 > ORDER BY TP.orderId > ) > SELECT group_concat(teamIDs,','), group_concat(teamNames,',') > FROM WTP > GROUP BY WTP.personId; > > > Again, you can just remove all the grouping words to reveal normal Query > listings. > > >> TeamTable >> +---+ >> | id | name | >> +---+ >> | 1 | blue| >> | 2 | green | >> +---| >> >> >> PersonTable >> +--+ >> | id | name | >> +--+ >> | 4 | john | >> | 5 | bill | >> +-+---+ >> >> TeamPersonTable >> +---+ >> | teamId | personId | orderId >> +--+ >> | 1 | 4 |1 >> | 1 | 5 |0 >> | 2 | 4 |0 >> | 2 | 5 |1 >> +-++--+ >> >> >> >> Query Input: teamId = 1 >> >> Result: >> >> personNames = "bill,john" >> personIds = "5,4" >> >> - >> >> Query Input: teamId = 2 >> >> Result: >> >> personNames = "john,bill" >> personIds = "4,5" >> > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > If you reply to this email, your message will be added to the discussion > below: > http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75802.html > > To unsubscribe from Simple Select from IN - from a newbie., visit > http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=75751&code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3NTF8MTk4Njk4NTgwNw== -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75804.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
On 2014/05/22 17:25, Humblebee wrote: Oops back sooner than I thought. I would like to order the grouping of the Persons in a particular order. So the sequence of the result is based on the TeamPersonTable's order field. First a word of warning - Please do not use column names that are the same as SQL keywords, such as "Order"... I took the liberty of renaming the new column to "orderId" because of this. Next, introducing a great new feature of SQLite (and most other SQL Engines) called CTE or Common Table Expression, which lets you define one kind of table and then extract a query from it, all in the same query. (This can also be achieved with Views if the SQL Engine doesn't support CTE, such as older SQLites, but this is usually a 2-step process which may or may not be more useful depending if you use the same base query for other purposes too). Here it is simply used to make up a sorted table, and then from it do the grouping and concat that is needed. Same set of Queries as before, but with ordering added - which again is made possible by that 1NF layout. Per Team Query: WITH WTP(teamId,personIDs,personNames) AS ( SELECT TP.teamId, P.id, P.name FROM TeamPersonTable TP LEFT JOIN PersonTable P ON P.id=TP.personId WHERE TP.teamid=1 ORDER BY TP.orderId ) SELECT group_concat(personIDs,','), group_concat(personNames,',') FROM WTP GROUP BY WTP.teamId; Per person Query: WITH WTP(personId,teamIDs,teamNames) AS ( SELECT P.id, T.id, T.name FROM TeamPersonTable TP LEFT JOIN TeamTable T ON T.id=TP.teamId LEFT JOIN PersonTable P ON P.id=TP.personId WHERE P.id=5 ORDER BY TP.orderId ) SELECT group_concat(teamIDs,','), group_concat(teamNames,',') FROM WTP GROUP BY WTP.personId; Again, you can just remove all the grouping words to reveal normal Query listings. TeamTable +---+ | id | name | +---+ | 1 | blue| | 2 | green | +---| PersonTable +--+ | id | name | +--+ | 4 | john | | 5 | bill | +-+---+ TeamPersonTable +---+ | teamId | personId | orderId +--+ | 1 | 4 |1 | 1 | 5 |0 | 2 | 4 |0 | 2 | 5 |1 +-++--+ Query Input: teamId = 1 Result: personNames = "bill,john" personIds = "5,4" - Query Input: teamId = 2 Result: personNames = "john,bill" personIds = "4,5" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem in query planner?
On Thu, May 22, 2014 at 5:34 PM, Abramo Bagnara wrote: > $ sqlite3 > SQLite version 3.8.4.3 2014-04-03 16:53:12 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> create table t(a, b, c); > sqlite> create index t_ab on t(a, b); > sqlite> create index t_ac on t(a, c); > sqlite> explain query plan select * from t where a = 1 and (b = 2 or c = > 2); > 0|0|0|SEARCH TABLE t USING INDEX t_ac (a=?) > sqlite> explain query plan select * from t where (a = 1 and b = 2) or (a > = 1 and c = 2); > 0|0|0|SEARCH TABLE t USING INDEX t_ab (a=? AND b=?) > 0|0|0|SEARCH TABLE t USING INDEX t_ac (a=? AND c=?) > > Although the two queries are equivalent the first form is not optimized > to use available indices. > > Is this expected? > Actually, SQLite believes (with good reason) that the first form is faster than the second. Using two indices to process a WHERE clause with OR terms is much faster than a full table scan, but it is also much more costly than using just a single index. So the first form is usually preferred. (That decision might come out differently if SQLite has access to index statistics created by ANALYZE but it comes out as shown above by default, and that is a reasonable default choice in the absence of additional information.) Curiously, SQLite will convert (a=? AND (b=? or c=?)) into ((a=? AND b=?) OR (a=? AND c=?)) as you can see by running the following: create table t(a,b,c); create index t_ac on t(a,c); create index t_bc on t(b,c); explain query plan select * from t where (a=1 or b=2) and c=3; 0|0|0|SEARCH INDEX t USING INDEX t_ac (a=? AND c=?) 0|0|0|SEARCH INDEX t USING INDEX t_bc (b=? AND c=?) But the query planner does not try to factor the WHERE clause and convert ((a=? AND b=?) OR (a=? AND c=?)) into (a=? AND (b=? OR c=?)), which would be necessary in order to get your second query to use the faster plan. > > -- > Abramo Bagnara > > BUGSENG srl - http://bugseng.com > mailto:abramo.bagn...@bugseng.com > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Release date of Windows Phone 8.1 SQLite
Klemen Forstneric wrote: > > is there a release date for the Windows Phone 8.1 version of SQLite (the > one available here: > http://visualstudiogallery.msdn.microsoft.com/5d97faf6-39e3-4048-a0bc-adde2a f75d1b) > The current plan is to release it when SQLite 3.8.5 is released. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem in query planner?
$ sqlite3 SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t(a, b, c); sqlite> create index t_ab on t(a, b); sqlite> create index t_ac on t(a, c); sqlite> explain query plan select * from t where a = 1 and (b = 2 or c = 2); 0|0|0|SEARCH TABLE t USING INDEX t_ac (a=?) sqlite> explain query plan select * from t where (a = 1 and b = 2) or (a = 1 and c = 2); 0|0|0|SEARCH TABLE t USING INDEX t_ab (a=? AND b=?) 0|0|0|SEARCH TABLE t USING INDEX t_ac (a=? AND c=?) Although the two queries are equivalent the first form is not optimized to use available indices. Is this expected? -- Abramo Bagnara BUGSENG srl - http://bugseng.com mailto:abramo.bagn...@bugseng.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Release date of Windows Phone 8.1 SQLite
Hey everyone, is there a release date for the Windows Phone 8.1 version of SQLite (the one available here: http://visualstudiogallery.msdn.microsoft.com/5d97faf6-39e3-4048-a0bc-adde2af75d1b) ? Cheers, Klemen Forstnerič ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
Oops back sooner than I thought. I would like to order the grouping of the Persons in a particular order. So the sequence of the result is based on the TeamPersonTable's order field. TeamTable +---+ | id | name | +---+ | 1 | blue| | 2 | green | +---| PersonTable +--+ | id | name | +--+ | 4 | john | | 5 | bill | +-+---+ TeamPersonTable +---+ | teamId | personId | order +--+ | 1 | 4 |1 | 1 | 5 |0 | 2 | 4 |0 | 2 | 5 |1 +-++--+ Query Input: teamId = 1 Result: personNames = "bill,john" personIds = "5,4" - Query Input: teamId = 2 Result: personNames = "john,bill" personIds = "4,5" On 5/22/14, fantasia dosa wrote: > RSmith, Two thumbs up for your super kind help. > > I will study the queries that you gave and try them backwards and > forwards to better understand the magic. Hope you don't mind if I ask > more newbie questions in the future. > > Cheers to the many nice people in this Great list. > > > On 5/22/14, fantasia dosa wrote: >> I'm very happy to have taken the advice of the Awesome people on this >> list to add an additional table and make the data normalized. From >> today, I learned that the word Normalized -> Magic. >> >> On 5/22/14, fantasia dosa wrote: >>> Ah yes, you are correct. >>> >>> It was my typo. Works perfectly. >>> >>> On 5/22/14, RSmith [via SQLite] >>> >>> wrote: I'm trying to understand the grouping magic and testing the first and last query. Only thing is for some reason, I'm not getting any result for the last query. Perhaps i'm doing something wrong. Works perfectly for me, are you sure you have it exactly so? Check the table names correspond to your tables maybe. (Though the other queries should fail too if that was a problem): Also ensure you have a Person in the PersonTable with id = 5. SELECT T.id AS TeamID, T.name AS TeamName, P.id AS PersonID, P.name AS PersonName FROM TeamPersonTable TP LEFT JOIN PersonTable P ON P.id=TP.personId LEFT JOIN TeamTable T ON T.id=TP.teamId WHERE P.id=5 On 2014/05/22 14:28, Humblebee wrote: > Wow, I just tried the "Group" thing and it's truly magic. Big Bang ! > The first query gave the Exact answer I was looking for. It even > assembled a string. Forgot to mention: These queries and subsequent amazement is only courtesy of those tables being in 1NF format now, it is what makes the queries possible to be linked up in the way (or in any other way you might dream up) - which is why other posters tried to urge you so hard to consider changing to this. Very glad you did. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ If you reply to this email, your message will be added to the discussion below: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75787.html To unsubscribe from Simple Select from IN - from a newbie., visit http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=75751&code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3NTF8MTk4Njk4NTgwNw== >>> >> > -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75797.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for shell .IMPORT improvement
On Thu, May 22, 2014 at 4:55 PM, Dominique Devienne wrote: > On Thu, May 22, 2014 at 3:26 PM, Noel Frankinet > wrote: > > I propose Musqlar, the Mighty universal sql Archiver :-) > > Sounds like Musk-lar, so you loose the S.Q.L. of sqlar which sounds to > me like C-quel-ar ;) > Maybe this is stretching it a bit, but how about... darh? -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for shell .IMPORT improvement
On Thu, May 22, 2014 at 3:26 PM, Noel Frankinet wrote: > I propose Musqlar, the Mighty universal sql Archiver :-) Sounds like Musk-lar, so you loose the S.Q.L. of sqlar which sounds to me like C-quel-ar ;) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Load time performance
On 22 May 2014, at 1:12pm, Hadashi, Rinat wrote: > The first time I access a database takes significantly more time than > subsequent accesses. > I am looking for ideas to shorten the time required for the first access. Access it before you need it. You understand that the first access needs to do more than later accesses, right ? I Depending on what more you're in it has to open the database file, read the schema, create a journal file, etc.. That's what's taking the time. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for shell .IMPORT improvement
I propose Musqlar, the Mighty universal sql Archiver :-) On 22 May 2014 15:22, Dominique Devienne wrote: > On Thu, May 22, 2014 at 1:51 PM, Richard Hipp wrote: > > How about "sqlar" for "SQL Archive"? http://www.sqlite.org/sqlar > > I like it! Sounds ominous when pronounced out loud :). > > Much better name IMHO, and no negative connotation in French that I > know of. --DD > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Noël Frankinet Strategis sprl 0478/90.92.54 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for shell .IMPORT improvement
On Thu, May 22, 2014 at 1:51 PM, Richard Hipp wrote: > How about "sqlar" for "SQL Archive"? http://www.sqlite.org/sqlar I like it! Sounds ominous when pronounced out loud :). Much better name IMHO, and no negative connotation in French that I know of. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Load time performance
You shouldn't access a SQLite database remotely, except maybe for read only access, but that could come under fire. Any network file action can't guarantee that write locks happen. If you attempt to access a file in READ-ONLY mode, you'll be greeted with a 1-5 second delay (I don't remember what the delay is right now) per connection. If you're using one connection to the DB, then you'll experience the delay. If you've got one connection going for the life of your application, you'll get hit with the first delay, but all subsequent queries will work. On Thu, May 22, 2014 at 8:12 AM, Hadashi, Rinat wrote: > Hi > > The first time I access a database takes significantly more time than > subsequent accesses. > I am looking for ideas to shorten the time required for the first access. > > I work on Linux, my db. file is "somewhere" in the file system, not > locally on the machine from which I am running sqlite3 > > Thanks > > Rinat Hadashi > > > - > Intel Israel (74) Limited > > This e-mail and any attachments may contain confidential material for > the sole use of the intended recipient(s). Any review or distribution > by others is strictly prohibited. If you are not the intended > recipient, please contact the sender and delete all copies. > ___ > 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] Simple Select from IN - from a newbie.
RSmith, Two thumbs up for your super kind help. I will study the queries that you gave and try them backwards and forwards to better understand the magic. Hope you don't mind if I ask more newbie questions in the future. Cheers to the many nice people in this Great list. On 5/22/14, fantasia dosa wrote: > I'm very happy to have taken the advice of the Awesome people on this > list to add an additional table and make the data normalized. From > today, I learned that the word Normalized -> Magic. > > On 5/22/14, fantasia dosa wrote: >> Ah yes, you are correct. >> >> It was my typo. Works perfectly. >> >> On 5/22/14, RSmith [via SQLite] >> wrote: >>> >>> >>> I'm trying to understand the grouping magic and testing the first and >>> last query. >>> Only thing is for some reason, I'm not getting any result for the last >>> query. Perhaps i'm doing something wrong. >>> >>> >>> Works perfectly for me, are you sure you have it exactly so? Check the >>> table >>> names correspond to your tables maybe. (Though the >>> other queries should fail too if that was a problem): >>> Also ensure you have a Person in the PersonTable with id = 5. >>> >>> SELECT T.id AS TeamID, T.name AS TeamName, P.id AS PersonID, P.name AS >>> PersonName >>> FROM TeamPersonTable TP >>> LEFT JOIN PersonTable P ON P.id=TP.personId >>> LEFT JOIN TeamTable T ON T.id=TP.teamId >>> WHERE P.id=5 >>> >>> >>> >>> On 2014/05/22 14:28, Humblebee wrote: Wow, I just tried the "Group" thing and it's truly magic. Big Bang ! The first query gave the Exact answer I was looking for. It even assembled a string. >>> >>> Forgot to mention: These queries and subsequent amazement is only >>> courtesy >>> of those tables being in 1NF format now, it is what makes >>> the queries possible to be linked up in the way (or in any other way you >>> might dream up) - which is why other posters tried to urge >>> you so hard to consider changing to this. Very glad you did. >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >>> >>> >>> ___ >>> If you reply to this email, your message will be added to the discussion >>> below: >>> http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75787.html >>> >>> To unsubscribe from Simple Select from IN - from a newbie., visit >>> http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=75751&code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3NTF8MTk4Njk4NTgwNw== >> > -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75790.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
I'm very happy to have taken the advice of the Awesome people on this list to add an additional table and make the data normalized. From today, I learned that the word Normalized -> Magic. On 5/22/14, fantasia dosa wrote: > Ah yes, you are correct. > > It was my typo. Works perfectly. > > On 5/22/14, RSmith [via SQLite] > wrote: >> >> >> I'm trying to understand the grouping magic and testing the first and >> last query. >> Only thing is for some reason, I'm not getting any result for the last >> query. Perhaps i'm doing something wrong. >> >> >> Works perfectly for me, are you sure you have it exactly so? Check the >> table >> names correspond to your tables maybe. (Though the >> other queries should fail too if that was a problem): >> Also ensure you have a Person in the PersonTable with id = 5. >> >> SELECT T.id AS TeamID, T.name AS TeamName, P.id AS PersonID, P.name AS >> PersonName >> FROM TeamPersonTable TP >> LEFT JOIN PersonTable P ON P.id=TP.personId >> LEFT JOIN TeamTable T ON T.id=TP.teamId >> WHERE P.id=5 >> >> >> >> On 2014/05/22 14:28, Humblebee wrote: >>> Wow, I just tried the "Group" thing and it's truly magic. Big Bang ! >>> The first query gave the Exact answer I was looking for. It even >>> assembled a string. >> >> Forgot to mention: These queries and subsequent amazement is only >> courtesy >> of those tables being in 1NF format now, it is what makes >> the queries possible to be linked up in the way (or in any other way you >> might dream up) - which is why other posters tried to urge >> you so hard to consider changing to this. Very glad you did. >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> >> >> ___ >> If you reply to this email, your message will be added to the discussion >> below: >> http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75787.html >> >> To unsubscribe from Simple Select from IN - from a newbie., visit >> http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=75751&code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3NTF8MTk4Njk4NTgwNw== > -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75789.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
Ah yes, you are correct. It was my typo. Works perfectly. On 5/22/14, RSmith [via SQLite] wrote: > > > I'm trying to understand the grouping magic and testing the first and > last query. > Only thing is for some reason, I'm not getting any result for the last > query. Perhaps i'm doing something wrong. > > > Works perfectly for me, are you sure you have it exactly so? Check the table > names correspond to your tables maybe. (Though the > other queries should fail too if that was a problem): > Also ensure you have a Person in the PersonTable with id = 5. > > SELECT T.id AS TeamID, T.name AS TeamName, P.id AS PersonID, P.name AS > PersonName > FROM TeamPersonTable TP > LEFT JOIN PersonTable P ON P.id=TP.personId > LEFT JOIN TeamTable T ON T.id=TP.teamId > WHERE P.id=5 > > > > On 2014/05/22 14:28, Humblebee wrote: >> Wow, I just tried the "Group" thing and it's truly magic. Big Bang ! >> The first query gave the Exact answer I was looking for. It even >> assembled a string. > > Forgot to mention: These queries and subsequent amazement is only courtesy > of those tables being in 1NF format now, it is what makes > the queries possible to be linked up in the way (or in any other way you > might dream up) - which is why other posters tried to urge > you so hard to consider changing to this. Very glad you did. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > If you reply to this email, your message will be added to the discussion > below: > http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75787.html > > To unsubscribe from Simple Select from IN - from a newbie., visit > http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=75751&code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3NTF8MTk4Njk4NTgwNw== -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75788.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
I'm trying to understand the grouping magic and testing the first and last query. Only thing is for some reason, I'm not getting any result for the last query. Perhaps i'm doing something wrong. Works perfectly for me, are you sure you have it exactly so? Check the table names correspond to your tables maybe. (Though the other queries should fail too if that was a problem): Also ensure you have a Person in the PersonTable with id = 5. SELECT T.id AS TeamID, T.name AS TeamName, P.id AS PersonID, P.name AS PersonName FROM TeamPersonTable TP LEFT JOIN PersonTable P ON P.id=TP.personId LEFT JOIN TeamTable T ON T.id=TP.teamId WHERE P.id=5 On 2014/05/22 14:28, Humblebee wrote: Wow, I just tried the "Group" thing and it's truly magic. Big Bang ! The first query gave the Exact answer I was looking for. It even assembled a string. Forgot to mention: These queries and subsequent amazement is only courtesy of those tables being in 1NF format now, it is what makes the queries possible to be linked up in the way (or in any other way you might dream up) - which is why other posters tried to urge you so hard to consider changing to this. Very glad you did. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
I'm trying to understand the grouping magic and testing the first and last query. Only thing is for some reason, I'm not getting any result for the last query. Perhaps i'm doing something wrong. On 5/22/14, RSmith [via SQLite] wrote: > > > > On 2014/05/22 14:28, Humblebee wrote: >> Thanks for your answers. >> >> Wow, I just tried the "Group" thing and it's truly magic. Big Bang ! >> The first query gave the Exact answer I was looking for. It even >> assembled a string. >> Amazing how this works. > > I agree :) > >> Is the last query the equivalent of the first query? > Ref: >> SELECT group_concat(P.id,','), group_concat(P.name,',') >> FROM TeamPersonTable TP >> LEFT JOIN PersonTable P ON P.id=TP.personId >> WHERE TP.teamid=1 >> GROUP BY TP.teamid; >> > vs. >> SELECT T.id AS TeamID, T.name AS TeamName, P.id AS PersonID, P.name AS >> PersonName >> FROM TeamPersonTable TP >> LEFT JOIN PersonTable P ON P.id=TP.personId >> LEFT JOIN TeamTable T ON T.id=TP.teamId >> WHERE P.id=5; > > Yes it is equivalent in principle I think - obviously the selected fields > and criteria changed, and the first one groups and csv's > it, while the last one lists them one by one - Is this what you meant? > Add some more test data and run all the queries, the > differences/similarities will become very clear. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > If you reply to this email, your message will be added to the discussion > below: > http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75785.html > > To unsubscribe from Simple Select from IN - from a newbie., visit > http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=75751&code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3NTF8MTk4Njk4NTgwNw== -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75786.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
On 2014/05/22 14:28, Humblebee wrote: Thanks for your answers. Wow, I just tried the "Group" thing and it's truly magic. Big Bang ! The first query gave the Exact answer I was looking for. It even assembled a string. Amazing how this works. I agree :) Is the last query the equivalent of the first query? Ref: SELECT group_concat(P.id,','), group_concat(P.name,',') FROM TeamPersonTable TP LEFT JOIN PersonTable P ON P.id=TP.personId WHERE TP.teamid=1 GROUP BY TP.teamid; vs. SELECT T.id AS TeamID, T.name AS TeamName, P.id AS PersonID, P.name AS PersonName FROM TeamPersonTable TP LEFT JOIN PersonTable P ON P.id=TP.personId LEFT JOIN TeamTable T ON T.id=TP.teamId WHERE P.id=5; Yes it is equivalent in principle I think - obviously the selected fields and criteria changed, and the first one groups and csv's it, while the last one lists them one by one - Is this what you meant? Add some more test data and run all the queries, the differences/similarities will become very clear. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
Thanks for your answers. Wow, I just tried the "Group" thing and it's truly magic. Big Bang ! The first query gave the Exact answer I was looking for. It even assembled a string. Amazing how this works. Is the last query the equivalent of the first query? On 5/22/14, RSmith [via SQLite] wrote: > > > > On 2014/05/22 13:39, Humblebee wrote: >> TeamTable >> +---+ >> | id | name | >> +---+ >> | 1 | blue| >> | 2 | green | >> +---| >> >> PersonTable >> +--+ >> | id | name | >> +--+ >> | 4 | john | >> | 5 | bill | >> +-+---+ >> >> TeamPersonTable >> +---+ >> | teamId | personId | >> +---+ >> | 1 | 4 | >> | 1 | 5 | >> | 2 | 4 | >> | 2 | 5 | >> +-+--+ >> >> So a person can belong to any teams. >> >> >> Query Input: teamId = 1 >> >> Result: >> >> personNames = "john,bill" >> personIds = "4,5" >> > > Ok that one is easy I think - Joins and Grouping are magic - try this: > > SELECT group_concat(P.id,','), group_concat(P.name,',') > FROM TeamPersonTable TP > LEFT JOIN PersonTable P ON P.id=TP.personId > WHERE TP.teamid=1 > GROUP BY TP.teamid; > > > Similarly, if you fancy to pick teams by names, something like this would > work: > > SELECT group_concat(P.id,','), group_concat(P.name,',') > FROM TeamPersonTable TP > LEFT JOIN PersonTable P ON P.id=TP.personId > LEFT JOIN TeamTable T ON T.id=TP.teamId > WHERE T.name LIKE 'blu%' > GROUP BY TP.teamid; > > > Or to see the teams to which a person belongs: > > SELECT group_concat(T.id,','), group_concat(T.name,',') > FROM TeamPersonTable TP > LEFT JOIN PersonTable P ON P.id=TP.personId > LEFT JOIN TeamTable T ON T.id=TP.teamId > WHERE P.name LIKE 'john%' > GROUP BY TP.personId; > > > And if you drop all the words with "group" in them, it will look like a > standard listed query: > > SELECT T.id AS TeamID, T.name AS TeamName, P.id AS PersonID, P.name AS > PersonName > FROM TeamPersonTable TP > LEFT JOIN PersonTable P ON P.id=TP.personId > LEFT JOIN TeamTable T ON T.id=TP.teamId > WHERE P.id=5; > > > Let us know if anything is unclear or you need it to work differently. > (Btw, WHERE P.id=5 will be much faster than WHERE P.name LIKE 'bill%' or > some such) > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > If you reply to this email, your message will be added to the discussion > below: > http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75782.html > > To unsubscribe from Simple Select from IN - from a newbie., visit > http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=75751&code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3NTF8MTk4Njk4NTgwNw== -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75784.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Load time performance
Hi The first time I access a database takes significantly more time than subsequent accesses. I am looking for ideas to shorten the time required for the first access. I work on Linux, my db. file is "somewhere" in the file system, not locally on the machine from which I am running sqlite3 Thanks Rinat Hadashi - Intel Israel (74) Limited This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
On 2014/05/22 13:39, Humblebee wrote: TeamTable +---+ | id | name | +---+ | 1 | blue| | 2 | green | +---| PersonTable +--+ | id | name | +--+ | 4 | john | | 5 | bill | +-+---+ TeamPersonTable +---+ | teamId | personId | +---+ | 1 | 4 | | 1 | 5 | | 2 | 4 | | 2 | 5 | +-+--+ So a person can belong to any teams. Query Input: teamId = 1 Result: personNames = "john,bill" personIds = "4,5" Ok that one is easy I think - Joins and Grouping are magic - try this: SELECT group_concat(P.id,','), group_concat(P.name,',') FROM TeamPersonTable TP LEFT JOIN PersonTable P ON P.id=TP.personId WHERE TP.teamid=1 GROUP BY TP.teamid; Similarly, if you fancy to pick teams by names, something like this would work: SELECT group_concat(P.id,','), group_concat(P.name,',') FROM TeamPersonTable TP LEFT JOIN PersonTable P ON P.id=TP.personId LEFT JOIN TeamTable T ON T.id=TP.teamId WHERE T.name LIKE 'blu%' GROUP BY TP.teamid; Or to see the teams to which a person belongs: SELECT group_concat(T.id,','), group_concat(T.name,',') FROM TeamPersonTable TP LEFT JOIN PersonTable P ON P.id=TP.personId LEFT JOIN TeamTable T ON T.id=TP.teamId WHERE P.name LIKE 'john%' GROUP BY TP.personId; And if you drop all the words with "group" in them, it will look like a standard listed query: SELECT T.id AS TeamID, T.name AS TeamName, P.id AS PersonID, P.name AS PersonName FROM TeamPersonTable TP LEFT JOIN PersonTable P ON P.id=TP.personId LEFT JOIN TeamTable T ON T.id=TP.teamId WHERE P.id=5; Let us know if anything is unclear or you need it to work differently. (Btw, WHERE P.id=5 will be much faster than WHERE P.name LIKE 'bill%' or some such) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for shell .IMPORT improvement
On Thu, May 22, 2014 at 7:46 AM, Richard Hipp wrote: > > > > On Thu, May 22, 2014 at 7:36 AM, Dominique Devienne > wrote: > >> On Thu, May 22, 2014 at 12:39 PM, Richard Hipp wrote: >> > Hyperlink has changed. It is now called "sfa" instead of "sar": >> >> I can't read "sfa" w/o remembering tv ads [1] for the toilet >> equivalent to a sink garbage disposal :) >> > > Good to know. Suggestions for a better name? > How about "sqlar" for "SQL Archive"? http://www.sqlite.org/sqlar > > >> >> Some acronyms don't cross language barriers w/o harm, like Audi's >> "e-tron" car [2]. --DD >> >> [1] https://www.google.com/search?q=pub+sanibroyeur+sfa >> [2] >> http://www.autoblog.com/2010/09/13/oh-crap-audi-mucks-up-e-tron-name-in-french/ >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > D. Richard Hipp > d...@sqlite.org > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for shell .IMPORT improvement
On Thu, May 22, 2014 at 7:36 AM, Dominique Devienne wrote: > On Thu, May 22, 2014 at 12:39 PM, Richard Hipp wrote: > > Hyperlink has changed. It is now called "sfa" instead of "sar": > > I can't read "sfa" w/o remembering tv ads [1] for the toilet > equivalent to a sink garbage disposal :) > Good to know. Suggestions for a better name? > > Some acronyms don't cross language barriers w/o harm, like Audi's > "e-tron" car [2]. --DD > > [1] https://www.google.com/search?q=pub+sanibroyeur+sfa > [2] > http://www.autoblog.com/2010/09/13/oh-crap-audi-mucks-up-e-tron-name-in-french/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
select p.id,p.name from TeamPersonTable tp join PersonTable p on (p.id = tp.personId) where tp.teamId = 1; -Ursprüngliche Nachricht- Von: Humblebee [mailto:fantasia.d...@gmail.com] Gesendet: Donnerstag, 22. Mai 2014 13:40 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Simple Select from IN - from a newbie. TeamTable +---+ | id | name | +---+ | 1 | blue| | 2 | green | +---| PersonTable +--+ | id | name | +--+ | 4 | john | | 5 | bill | +-+---+ TeamPersonTable +---+ | teamId | personId | +---+ | 1 | 4 | | 1 | 5 | | 2 | 4 | | 2 | 5 | +-+--+ So a person can belong to any teams. Query Input: teamId = 1 Result: personNames = "john,bill" personIds = "4,5" On 5/22/14, RSmith [via SQLite] wrote: > > > > On 2014/05/22 13:02, Humblebee wrote: >> @RSmith, >> >> Very much appreciate you taking the time to write such a detailed and >> awesome explanation of how the string and list works in SQL. I had >> no idea what goes on there. >> > > It is my pleasure, sadly I know exactly how it feels to be under the > wrong impression (I have been so on this very list!). > >> As for now, I'm following the good advice of all the much more >> knowledgeable people on this list to normalize the data by adding a >> Person_Team table to the database. My last question is what SQL >> statement could I use to get the information out of these 3 tables. > > That is great news- I promise you will not be sorry. As for the > question, once you decided how the tables will look, could you again > paste them here with a little bit of example data in each and then say > exactly how you want the resulting data to look after the query is run > - and we will try suggest the most optimized ways of achieving it. A > bit of knowledge about how often the tables will be updated/inserted > to, and how often the query will be run, and how much every table is > expected to grow over time, would all help to make the best decisions. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > If you reply to this email, your message will be added to the > discussion > below: > http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbi > e-tp75751p75776.html > > To unsubscribe from Simple Select from IN - from a newbie., visit > http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=uns > ubscribe_by_code&node=75751&code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3N > TF8MTk4Njk4NTgwNw== -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75778.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
TeamTable +---+ | id | name | +---+ | 1 | blue| | 2 | green | +---| PersonTable +--+ | id | name | +--+ | 4 | john | | 5 | bill | +-+---+ TeamPersonTable +---+ | teamId | personId | +---+ | 1 | 4 | | 1 | 5 | | 2 | 4 | | 2 | 5 | +-+--+ So a person can belong to any teams. Query Input: teamId = 1 Result: personNames = "john,bill" personIds = "4,5" On 5/22/14, RSmith [via SQLite] wrote: > > > > On 2014/05/22 13:02, Humblebee wrote: >> @RSmith, >> >> Very much appreciate you taking the time to write such a detailed and >> awesome explanation of how the string and list works in SQL. I had no >> idea what goes on there. >> > > It is my pleasure, sadly I know exactly how it feels to be under the wrong > impression (I have been so on this very list!). > >> As for now, I'm following the good advice of all the much more >> knowledgeable people on this list to normalize the data by adding a >> Person_Team table to the database. My last question is what SQL >> statement could I use to get the information out of these 3 tables. > > That is great news- I promise you will not be sorry. As for the question, > once you decided how the tables will look, could you again > paste them here with a little bit of example data in each and then say > exactly how you want the resulting data to look after the > query is run - and we will try suggest the most optimized ways of achieving > it. A bit of knowledge about how often the tables will > be updated/inserted to, and how often the query will be run, and how much > every table is expected to grow over time, would all help > to make the best decisions. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > If you reply to this email, your message will be added to the discussion > below: > http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75776.html > > To unsubscribe from Simple Select from IN - from a newbie., visit > http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=75751&code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3NTF8MTk4Njk4NTgwNw== -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75778.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for shell .IMPORT improvement
On Thu, May 22, 2014 at 12:39 PM, Richard Hipp wrote: > Hyperlink has changed. It is now called "sfa" instead of "sar": I can't read "sfa" w/o remembering tv ads [1] for the toilet equivalent to a sink garbage disposal :) Some acronyms don't cross language barriers w/o harm, like Audi's "e-tron" car [2]. --DD [1] https://www.google.com/search?q=pub+sanibroyeur+sfa [2] http://www.autoblog.com/2010/09/13/oh-crap-audi-mucks-up-e-tron-name-in-french/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
On 2014/05/22 13:02, Humblebee wrote: @RSmith, Very much appreciate you taking the time to write such a detailed and awesome explanation of how the string and list works in SQL. I had no idea what goes on there. It is my pleasure, sadly I know exactly how it feels to be under the wrong impression (I have been so on this very list!). As for now, I'm following the good advice of all the much more knowledgeable people on this list to normalize the data by adding a Person_Team table to the database. My last question is what SQL statement could I use to get the information out of these 3 tables. That is great news- I promise you will not be sorry. As for the question, once you decided how the tables will look, could you again paste them here with a little bit of example data in each and then say exactly how you want the resulting data to look after the query is run - and we will try suggest the most optimized ways of achieving it. A bit of knowledge about how often the tables will be updated/inserted to, and how often the query will be run, and how much every table is expected to grow over time, would all help to make the best decisions. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
To be more clear. So now I have 3 tables. Person Team Person_Team Query: 1. Get all persons that belongs to a certain team. The output should be: persons = "name1,name2,name3" personIds = "id1,id2,id3" So in other words, each field is a string delimited by a comma. On 5/22/14, fantasia dosa wrote: > @RSmith, > > Very much appreciate you taking the time to write such a detailed and > awesome explanation of how the string and list works in SQL. I had no > idea what goes on there. > > I also liked the car-jacking example, very funny - that made the > entire thing very clear. You're right, I wouldn't expect 4 cars out > of the same green color. > > As for now, I'm following the good advice of all the much more > knowledgeable people on this list to normalize the data by adding a > Person_Team table to the database. My last question is what SQL > statement could I use to get the information out of these 3 tables. > > On 5/22/14, RSmith [via SQLite] > wrote: >> >> >> >> On 2014/05/22 11:39, Humblebee wrote: >>> Thank you everyone for your kind input and suggestions. That is quite >>> a lot to consider. I didn't realize it would be so difficult for a >>> Select statement to return a string. >>> >>> Out of curiosity, when I do: >>> >>> SELECT * >>> FROM Person WHERE id IN(2,2,3,3) >>> >>> It doesn't seem to give back 4 rows, only 2. Looks like the >>> duplicates are not being returned. Is there someway to get back all 4 >>> rows. >> >> >> My good man, you need to be more clear in your questions. I'm sure 90% of >> the people on this list all know what is wrong with your >> query and what can and cannot work... however, most wont't reply because >> they are very unsure what you are trying to achieve. If >> it's just row duplication for the sake of it, well that's easy, you can >> use >> a join or a union. This is however very likely not what >> you are trying to achieve - so I will try to be more clear. >> >> Firstly, it is VERY easy for the query to return a string, it does that >> all >> day every day.. returning strings is what queries do >> best. What you want is NOT a string, what you want is a SQL query >> specification parameter... (which happens to be supplied in string >> format), but there is a very important difference, the specifier needs to >> be >> known fully when the query is prepared, i.e. BEFORE it >> is run and stepped through. You can see now that it cannot "wait" until >> the >> query has run a little bit before it actually gets >> another string which tries to tell it HOW to run. I hope this is clear to >> you.. it's not hard to return the string, it is hard (no, >> impossible) to introduce that string (or whatever else) halfway through >> the >> execution of the query /AS/ a specification for how the >> query should execute. Which is what your original question tried to >> achieve. >> >> I believe the reason why you do not understand the difference is that you >> are under the impression that the IN operator looks for a >> value in a string... which it doesn't, it looks for a value in a LIST >> that is why the string is useless. A list is a set of >> distinct values typically returned by a sub query or some specifier that >> lives in memory and can be looked up at any time during the >> query. The list cannot change halfway through (unless it is the result of >> a >> subquery), which again, is a LIST and not a string. The >> fact that your string seems (in human terms) to be recognizable as a LIST >> is >> pure coincidence and does not magically turn it into a >> LIST. SQL is very apt at returning lists too by the way, not just >> strings, >> but again, the list cannot be magically made up halfway >> through the query (in fact, at every step as per your suggestion), it >> needs >> to be known at the point of preparing the query, or be a >> result of a sub-query. >> >> To be clear, here are some queries that can and cannot work: >> >> SELECT a,b,c FROM t WHERE a IN (1,2,5); >> -- Valid Query because (1,2,5) is a list which can be compiled (even if >> from >> a set of characters, aka a string) and understood at >> preparation time. >> >> SELECT a,b,c FROM t WHERE 3 IN a; >> -- Invalid - a is a string, not a list, even if it looks like a list to >> you >> now, the Query planner has no way of knowing what the >> value will be in actual execution. Even if it looks valid to you and as >> if >> it can be compiled on the roll, it may at any point >> during execution have a value like "Cherry Cream Pie"... what happens >> then? >> How would that translate into a list? >> >> SELECT a,b,c FROM t WHERE a IN (1,1,1,1); >> -- This is valid, but will only ever return a results (or results) where >> a >> is exactly 1. It won't return the same result 4 times, >> because the IN specifies a check to see whether the record field is found >> in >> the list or not.. the list doesn't specify how many >> results there must be. >> It's like you being the car-jacking pitboss and you ask me, you
Re: [sqlite] Simple Select from IN - from a newbie.
@RSmith, Very much appreciate you taking the time to write such a detailed and awesome explanation of how the string and list works in SQL. I had no idea what goes on there. I also liked the car-jacking example, very funny - that made the entire thing very clear. You're right, I wouldn't expect 4 cars out of the same green color. As for now, I'm following the good advice of all the much more knowledgeable people on this list to normalize the data by adding a Person_Team table to the database. My last question is what SQL statement could I use to get the information out of these 3 tables. On 5/22/14, RSmith [via SQLite] wrote: > > > > On 2014/05/22 11:39, Humblebee wrote: >> Thank you everyone for your kind input and suggestions. That is quite >> a lot to consider. I didn't realize it would be so difficult for a >> Select statement to return a string. >> >> Out of curiosity, when I do: >> >> SELECT * >> FROM Person WHERE id IN(2,2,3,3) >> >> It doesn't seem to give back 4 rows, only 2. Looks like the >> duplicates are not being returned. Is there someway to get back all 4 >> rows. > > > My good man, you need to be more clear in your questions. I'm sure 90% of > the people on this list all know what is wrong with your > query and what can and cannot work... however, most wont't reply because > they are very unsure what you are trying to achieve. If > it's just row duplication for the sake of it, well that's easy, you can use > a join or a union. This is however very likely not what > you are trying to achieve - so I will try to be more clear. > > Firstly, it is VERY easy for the query to return a string, it does that all > day every day.. returning strings is what queries do > best. What you want is NOT a string, what you want is a SQL query > specification parameter... (which happens to be supplied in string > format), but there is a very important difference, the specifier needs to be > known fully when the query is prepared, i.e. BEFORE it > is run and stepped through. You can see now that it cannot "wait" until the > query has run a little bit before it actually gets > another string which tries to tell it HOW to run. I hope this is clear to > you.. it's not hard to return the string, it is hard (no, > impossible) to introduce that string (or whatever else) halfway through the > execution of the query /AS/ a specification for how the > query should execute. Which is what your original question tried to > achieve. > > I believe the reason why you do not understand the difference is that you > are under the impression that the IN operator looks for a > value in a string... which it doesn't, it looks for a value in a LIST > that is why the string is useless. A list is a set of > distinct values typically returned by a sub query or some specifier that > lives in memory and can be looked up at any time during the > query. The list cannot change halfway through (unless it is the result of a > subquery), which again, is a LIST and not a string. The > fact that your string seems (in human terms) to be recognizable as a LIST is > pure coincidence and does not magically turn it into a > LIST. SQL is very apt at returning lists too by the way, not just strings, > but again, the list cannot be magically made up halfway > through the query (in fact, at every step as per your suggestion), it needs > to be known at the point of preparing the query, or be a > result of a sub-query. > > To be clear, here are some queries that can and cannot work: > > SELECT a,b,c FROM t WHERE a IN (1,2,5); > -- Valid Query because (1,2,5) is a list which can be compiled (even if from > a set of characters, aka a string) and understood at > preparation time. > > SELECT a,b,c FROM t WHERE 3 IN a; > -- Invalid - a is a string, not a list, even if it looks like a list to you > now, the Query planner has no way of knowing what the > value will be in actual execution. Even if it looks valid to you and as if > it can be compiled on the roll, it may at any point > during execution have a value like "Cherry Cream Pie"... what happens then? > How would that translate into a list? > > SELECT a,b,c FROM t WHERE a IN (1,1,1,1); > -- This is valid, but will only ever return a results (or results) where a > is exactly 1. It won't return the same result 4 times, > because the IN specifies a check to see whether the record field is found in > the list or not.. the list doesn't specify how many > results there must be. > It's like you being the car-jacking pitboss and you ask me, your faithful > GTA expert, to find and bring you a car that is either > green or green or green or green. > Would you expect to get 4 cars? (Hopefully not!) > > Now if you can devise a query so that it returns that list string as a list > (query result) and use it as a sub-query inside the main > query, that will work, but be really slow (I think that was one of the > mentioned solutions). > > I hope all this makes more clear why we cant offer much help with ho
Re: [sqlite] Simple Select from IN - from a newbie.
On 2014/05/22 11:39, Humblebee wrote: Thank you everyone for your kind input and suggestions. That is quite a lot to consider. I didn't realize it would be so difficult for a Select statement to return a string. Out of curiosity, when I do: SELECT * FROM Person WHERE id IN(2,2,3,3) It doesn't seem to give back 4 rows, only 2. Looks like the duplicates are not being returned. Is there someway to get back all 4 rows. My good man, you need to be more clear in your questions. I'm sure 90% of the people on this list all know what is wrong with your query and what can and cannot work... however, most wont't reply because they are very unsure what you are trying to achieve. If it's just row duplication for the sake of it, well that's easy, you can use a join or a union. This is however very likely not what you are trying to achieve - so I will try to be more clear. Firstly, it is VERY easy for the query to return a string, it does that all day every day.. returning strings is what queries do best. What you want is NOT a string, what you want is a SQL query specification parameter... (which happens to be supplied in string format), but there is a very important difference, the specifier needs to be known fully when the query is prepared, i.e. BEFORE it is run and stepped through. You can see now that it cannot "wait" until the query has run a little bit before it actually gets another string which tries to tell it HOW to run. I hope this is clear to you.. it's not hard to return the string, it is hard (no, impossible) to introduce that string (or whatever else) halfway through the execution of the query /AS/ a specification for how the query should execute. Which is what your original question tried to achieve. I believe the reason why you do not understand the difference is that you are under the impression that the IN operator looks for a value in a string... which it doesn't, it looks for a value in a LIST that is why the string is useless. A list is a set of distinct values typically returned by a sub query or some specifier that lives in memory and can be looked up at any time during the query. The list cannot change halfway through (unless it is the result of a subquery), which again, is a LIST and not a string. The fact that your string seems (in human terms) to be recognizable as a LIST is pure coincidence and does not magically turn it into a LIST. SQL is very apt at returning lists too by the way, not just strings, but again, the list cannot be magically made up halfway through the query (in fact, at every step as per your suggestion), it needs to be known at the point of preparing the query, or be a result of a sub-query. To be clear, here are some queries that can and cannot work: SELECT a,b,c FROM t WHERE a IN (1,2,5); -- Valid Query because (1,2,5) is a list which can be compiled (even if from a set of characters, aka a string) and understood at preparation time. SELECT a,b,c FROM t WHERE 3 IN a; -- Invalid - a is a string, not a list, even if it looks like a list to you now, the Query planner has no way of knowing what the value will be in actual execution. Even if it looks valid to you and as if it can be compiled on the roll, it may at any point during execution have a value like "Cherry Cream Pie"... what happens then? How would that translate into a list? SELECT a,b,c FROM t WHERE a IN (1,1,1,1); -- This is valid, but will only ever return a results (or results) where a is exactly 1. It won't return the same result 4 times, because the IN specifies a check to see whether the record field is found in the list or not.. the list doesn't specify how many results there must be. It's like you being the car-jacking pitboss and you ask me, your faithful GTA expert, to find and bring you a car that is either green or green or green or green. Would you expect to get 4 cars? (Hopefully not!) Now if you can devise a query so that it returns that list string as a list (query result) and use it as a sub-query inside the main query, that will work, but be really slow (I think that was one of the mentioned solutions). I hope all this makes more clear why we cant offer much help with how you think it ought to be done.. because that assumption is not correct. Say exactly what you need or intend, and we'd try our best to help you solve it, but the ideal has to be coherent and sound. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for shell .IMPORT improvement
On Thu, May 22, 2014 at 4:22 AM, Kees Nuyt wrote: > On Sun, 18 May 2014 01:06:42 +0300, wrote: > > > PS. By the way, any progress on the shell enhancement to load/save blobs? > > This will make the shell capable of handling any database without the > need > > for external programs. > > I think SQLite File Archiver comes very close: > http://www.sqlite.org/sar/doc/trunk/README.md > Hyperlink has changed. It is now called "sfa" instead of "sar": http://www.sqlite.org/sfa The old link ("sar") still works, but will eventually be removed. > > -- > Groet, Cordialement, Pozdrawiam, Regards, > > Kees Nuyt > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
Looks like all roads are pointing to creating a Normalized table structure. So if I were to add another table Person_Team table and not use the string as everyone is suggesting, then how would the SQL statement look to retrieve the data. On 5/22/14, Hick Gunter [via SQLite] wrote: > > > No. The internal table stores only unique keys. > > -Ursprüngliche Nachricht- > Von: Humblebee [mailto:fantasia.d...@gmail.com] > Gesendet: Donnerstag, 22. Mai 2014 11:39 > An: sqlite-users@sqlite.org > Betreff: Re: [sqlite] Simple Select from IN - from a newbie. > > Thank you everyone for your kind input and suggestions. That is quite a lot > to consider. I didn't realize it would be so difficult for a Select > statement to return a string. > > Out of curiosity, when I do: > > SELECT * > FROM Person WHERE id IN(2,2,3,3) > > It doesn't seem to give back 4 rows, only 2. Looks like the duplicates are > not being returned. Is there someway to get back all 4 rows. > > > > On 5/22/14, Keith Medcalf [via SQLite] > wrote: >> >> >> Andy Goth wrote: >> >>>And honestly, please don't give people with no knowledge of SQL theory >>>the power to set your SQL schema in stone. >> >> I am sure you mean Relational Theory, when using a database >> implementing Relational semantics, such as SQLite. >> >> There is no requirement that SQL (Structured Query Language) be >> implemented to query a Relational Database. There are many >> implementations which use SQL to query data from hierarchical, >> network, network extended, and a myriad of other underlying database >> storage mechanisms. SQL no more binds the relational model than using >> COBOL (a computer programming language) binds the implementation to a 4341 >> SysPlex running OS/VS1. >> >> SQLite implements an SQL interface using a relational access model >> against an ISAM datastore. Storing mutivalued (array) items is a >> violation of the Relational Model, not SQL and not ISAM. If you used, >> for example, ADABAS, then you could store arrays in a table field and >> perform SQL operations against them as if they were a BCNF normalized >> relational N:M join table. >> Some other not-so-relational relational databases support nonstandard >> means of achieving the same thing. >> >> >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> >> >> ___ >> If you reply to this email, your message will be added to the >> discussion >> below: >> http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbi >> e-tp75751p75767.html >> >> To unsubscribe from Simple Select from IN - from a newbie., visit >> http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=uns >> ubscribe_by_code&node=75751&code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3N >> TF8MTk4Njk4NTgwNw== > > > > > -- > View this message in context: > http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75769.html > Sent from the SQLite mailing list archive at Nabble.com. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > --- > Gunter Hick > Software Engineer > > Scientific Games International GmbH > Klitschgasse 2 – 4, A - 1130 Vienna, > Austria > FN 157284 a, HG Wien > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This e-mail is confidential and may well also be legally privileged. If you > have received it in error, you are on notice as to its status and > accordingly please notify us immediately by reply e-mail and then > delete this message from your system. Please do not copy it or use it for > any purposes, or disclose its contents to any person as to do so could be a > breach of confidence. Thank you for your cooperation. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > If you reply to this email, your message will be added to the discussion > below: > http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75770.html > > To unsubscribe from Simple Select from IN - from a newbie., visit > http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=75751&code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3NTF8MTk4Njk4NTgwNw== -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75771.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
No. The internal table stores only unique keys. -Ursprüngliche Nachricht- Von: Humblebee [mailto:fantasia.d...@gmail.com] Gesendet: Donnerstag, 22. Mai 2014 11:39 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Simple Select from IN - from a newbie. Thank you everyone for your kind input and suggestions. That is quite a lot to consider. I didn't realize it would be so difficult for a Select statement to return a string. Out of curiosity, when I do: SELECT * FROM Person WHERE id IN(2,2,3,3) It doesn't seem to give back 4 rows, only 2. Looks like the duplicates are not being returned. Is there someway to get back all 4 rows. On 5/22/14, Keith Medcalf [via SQLite] wrote: > > > Andy Goth wrote: > >>And honestly, please don't give people with no knowledge of SQL theory >>the power to set your SQL schema in stone. > > I am sure you mean Relational Theory, when using a database > implementing Relational semantics, such as SQLite. > > There is no requirement that SQL (Structured Query Language) be > implemented to query a Relational Database. There are many > implementations which use SQL to query data from hierarchical, > network, network extended, and a myriad of other underlying database > storage mechanisms. SQL no more binds the relational model than using > COBOL (a computer programming language) binds the implementation to a 4341 > SysPlex running OS/VS1. > > SQLite implements an SQL interface using a relational access model > against an ISAM datastore. Storing mutivalued (array) items is a > violation of the Relational Model, not SQL and not ISAM. If you used, > for example, ADABAS, then you could store arrays in a table field and > perform SQL operations against them as if they were a BCNF normalized > relational N:M join table. > Some other not-so-relational relational databases support nonstandard > means of achieving the same thing. > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > If you reply to this email, your message will be added to the > discussion > below: > http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbi > e-tp75751p75767.html > > To unsubscribe from Simple Select from IN - from a newbie., visit > http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=uns > ubscribe_by_code&node=75751&code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3N > TF8MTk4Njk4NTgwNw== -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75769.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
Thank you everyone for your kind input and suggestions. That is quite a lot to consider. I didn't realize it would be so difficult for a Select statement to return a string. Out of curiosity, when I do: SELECT * FROM Person WHERE id IN(2,2,3,3) It doesn't seem to give back 4 rows, only 2. Looks like the duplicates are not being returned. Is there someway to get back all 4 rows. On 5/22/14, Keith Medcalf [via SQLite] wrote: > > > Andy Goth wrote: > >>And honestly, please don't give people with no knowledge of >>SQL theory the power to set your SQL schema in stone. > > I am sure you mean Relational Theory, when using a database implementing > Relational semantics, such as SQLite. > > There is no requirement that SQL (Structured Query Language) be implemented > to query a Relational Database. There are many implementations which use > SQL to query data from hierarchical, network, network extended, and a myriad > of other underlying database storage mechanisms. SQL no more binds the > relational model than using COBOL (a computer programming language) binds > the implementation to a 4341 SysPlex running OS/VS1. > > SQLite implements an SQL interface using a relational access model against > an ISAM datastore. Storing mutivalued (array) items is a violation of the > Relational Model, not SQL and not ISAM. If you used, for example, ADABAS, > then you could store arrays in a table field and perform SQL operations > against them as if they were a BCNF normalized relational N:M join table. > Some other not-so-relational relational databases support nonstandard means > of achieving the same thing. > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > If you reply to this email, your message will be added to the discussion > below: > http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75767.html > > To unsubscribe from Simple Select from IN - from a newbie., visit > http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=75751&code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3NTF8MTk4Njk4NTgwNw== -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75769.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for shell .IMPORT improvement
On Sun, 18 May 2014 01:06:42 +0300, wrote: > PS. By the way, any progress on the shell enhancement to load/save blobs? > This will make the shell capable of handling any database without the need > for external programs. I think SQLite File Archiver comes very close: http://www.sqlite.org/sar/doc/trunk/README.md -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users