Re: [sqlite] Simple Select from IN - from a newbie.

[Humblebee]

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=75751=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.

[RSmith]

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?

[Richard Hipp]

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

[Joe Mistachkin]

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?

[Abramo Bagnara]

$ 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

[Klemen Forstnerič]

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.

[Humblebee]

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=75751=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

[Stephan Beal]

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

[Dominique Devienne]

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

[Simon Slavin]

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

[Noel Frankinet]

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

[Dominique Devienne]

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

[Stephen Chrzanowski]

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.

[Humblebee]

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=75751=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.

[Humblebee]

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=75751=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.

[Humblebee]

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=75751=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.

[RSmith]

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.

[Humblebee]

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=75751=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.

[RSmith]

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.

[Humblebee]

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=75751=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

[Hadashi, Rinat]

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.

[RSmith]

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

[Richard Hipp]

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

[Richard Hipp]

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.

[Hick Gunter]

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=75751=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.

[Humblebee]

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=75751=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

[Dominique Devienne]

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.

[RSmith]

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.

[Humblebee]

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.

Re: [sqlite] Simple Select from IN - from a newbie.

[Humblebee]

@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 

Re: [sqlite] Simple Select from IN - from a newbie.

[RSmith]

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

[Richard Hipp]

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.

[Humblebee]

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=75751=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=75751=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

Re: [sqlite] Simple Select from IN - from a newbie.

[Hick Gunter]

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=75751=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.

[Humblebee]

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=75751=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

[Kees Nuyt]

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