Simple Select from IN - from a newbie.

classic Classic list List threaded Threaded
56 messages Options
123
Reply | Threaded
Open this post in threaded view
|

Simple Select from IN - from a newbie.

Humblebee
Hi all wonderful people on this list.

I'm a newbilie so my questions might seem very well-- kinda dumb so
please forgive me.

I'm trying to do the following in Sqlite.

TeamTable -
    parId: 4
    personIDs :  1,5,9,6

PersonTable -
   id:
   name:

SELECT * FROM PersonTable WHERE id IN (SELECT personIDs FROM
PersonTable WHERE parId = 4);

The result from the above query is empty whereas when I do:
SELECT * FROM PersonTable WHERE id IN(1,5,9,6)

Then I get the result that I need.

Any help is greatly appreciated.

Cheers.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

Simon Slavin-3

On 21 May 2014, at 1:59pm, fantasia dosa <[hidden email]> wrote:

> TeamTable -
>    parId: 4
>    personIDs :  1,5,9,6
>
> PersonTable -
>   id:
>   name:
>
> SELECT * FROM PersonTable WHERE id IN (SELECT personIDs FROM
> PersonTable WHERE parId = 4);

Your problem is cause by the way you're keeping your data.  I would like to suggest a better way, which is often called 'normal form' by people who use SQL.  There are two possibilities:

A) Each person is in either zero or one team: Remove 'personIDs'; add a 'teamID' column to your PersonTable.  If you have a person who is currently not in any team, set the column to 0.

B) Each person can be in more than one team: Remove 'personIDs'; create a new table 'PeopleInTeamsTable' which has two columns: teamID and personID.

Doing either of these will give you a simple SELECT command to select people details.  You will probably end up using JOIN something like this:

SELECT PersonTable.* FROM PersonTable
        JOIN TeamTable ON PersonTable.teamID = TeamTable.id
        WHERE TeamTable.parId = 4

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

Hick Gunter
In reply to this post by Humblebee
You show a field parId in your TeamTable, but select it from the PersonTable. Maybe you mean

SELECT * FROM PersonTable WHERE id IN (SELECT personIDs FROM TeamTable WHERE parId = 4);

-----Ursprüngliche Nachricht-----
Von: fantasia dosa [mailto:[hidden email]]
Gesendet: Mittwoch, 21. Mai 2014 15:00
An: [hidden email]
Betreff: [sqlite] Simple Select from IN - from a newbie.

Hi all wonderful people on this list.

I'm a newbilie so my questions might seem very well-- kinda dumb so please forgive me.

I'm trying to do the following in Sqlite.

TeamTable -
    parId: 4
    personIDs :  1,5,9,6

PersonTable -
   id:
   name:

SELECT * FROM PersonTable WHERE id IN (SELECT personIDs FROM PersonTable WHERE parId = 4);

The result from the above query is empty whereas when I do:
SELECT * FROM PersonTable WHERE id IN(1,5,9,6)

Then I get the result that I need.

Any help is greatly appreciated.

Cheers.
_______________________________________________
sqlite-users mailing list
[hidden email]
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: [hidden email]

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

Humblebee
Thank you for your replies.

I'm sorry for not being super clear about the problem:

Note: The parId belongs to another table not shown here.

TeamTable
+---------------------------------+
| id         | parId | personIds |
+---------------------------------+
| 1          | 4       | 1,5,2,3,4   |
| 2          | 5       | 2,6,3,5,1   |
+----------------------------------|

PersonTable
+------------------+
| id         | name |
+------------------+
| 4          | 4       |
| 5          | 5       |
+---------+-------+

@Simon, thank you for showing me a better way to setup the tables,
your way makes much more sense,  only problem is that in this
situation, the tables have already been defined and made by someone
else so I cannot change it.  I'm a bit stuck with the way it is.

@ Gunter, the picture above is a better representation of the tables.

After searching on the net for an answer, I think what I want is a way
to get a subSelect statement to return something  that the Select from
IN (string) can work.  At least this is what I'm thinking from my very
very limited understanding of SQL and with the way that I'm trying to
do this.




On 5/21/14, Hick Gunter [via SQLite]
<[hidden email]> wrote:

>
>
> You show a field parId in your TeamTable, but select it from the
> PersonTable. Maybe you mean
>
> SELECT * FROM PersonTable WHERE id IN (SELECT personIDs FROM TeamTable WHERE
> parId = 4);
>
> -----Ursprüngliche Nachricht-----
> Von: fantasia dosa [mailto:[hidden email]]
> Gesendet: Mittwoch, 21. Mai 2014 15:00
> An: [hidden email]
> Betreff: [sqlite] Simple Select from IN - from a newbie.
>
> Hi all wonderful people on this list.
>
> I'm a newbilie so my questions might seem very well-- kinda dumb so please
> forgive me.
>
> I'm trying to do the following in Sqlite.
>
> TeamTable -
>     parId: 4
>     personIDs :  1,5,9,6
>
> PersonTable -
>    id:
>    name:
>
> SELECT * FROM PersonTable WHERE id IN (SELECT personIDs FROM PersonTable
> WHERE parId = 4);
>
> The result from the above query is empty whereas when I do:
> SELECT * FROM PersonTable WHERE id IN(1,5,9,6)
>
> Then I get the result that I need.
>
> Any help is greatly appreciated.
>
> Cheers.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> 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: [hidden email]
>
> 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
> [hidden email]
> 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-tp75751p75753.html
>
> To unsubscribe from Simple Select from IN - from a newbie., visit
>
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

Humblebee
In reply to this post by Hick Gunter
Sorry, clicked the send button too quickly.

PersonTable
+------------------+
| id         | name |
+------------------+
| 4          | john   |
| 5          | taylor |
+---------+-------+

On 5/21/14, fantasia dosa <[hidden email]> wrote:

> Thank you for your replies.
>
> I'm sorry for not being super clear about the problem:
>
> Note: The parId belongs to another table not shown here.
>
> TeamTable
> +---------------------------------+
> | id         | parId | personIds |
> +---------------------------------+
> | 1          | 4       | 1,5,2,3,4   |
> | 2          | 5       | 2,6,3,5,1   |
> +----------------------------------|
>
> PersonTable
> +------------------+
> | id         | name |
> +------------------+
> | 4          | 4       |
> | 5          | 5       |
> +---------+-------+
>
> @Simon, thank you for showing me a better way to setup the tables,
> your way makes much more sense,  only problem is that in this
> situation, the tables have already been defined and made by someone
> else so I cannot change it.  I'm a bit stuck with the way it is.
>
> @ Gunter, the picture above is a better representation of the tables.
>
> After searching on the net for an answer, I think what I want is a way
> to get a subSelect statement to return something  that the Select from
> IN (string) can work.  At least this is what I'm thinking from my very
> very limited understanding of SQL and with the way that I'm trying to
> do this.
>
>
>
>
> On 5/21/14, Hick Gunter [via SQLite]
> <[hidden email]> wrote:
>>
>>
>> You show a field parId in your TeamTable, but select it from the
>> PersonTable. Maybe you mean
>>
>> SELECT * FROM PersonTable WHERE id IN (SELECT personIDs FROM TeamTable
>> WHERE
>> parId = 4);
>>
>> -----Ursprüngliche Nachricht-----
>> Von: fantasia dosa [mailto:[hidden email]]
>> Gesendet: Mittwoch, 21. Mai 2014 15:00
>> An: [hidden email]
>> Betreff: [sqlite] Simple Select from IN - from a newbie.
>>
>> Hi all wonderful people on this list.
>>
>> I'm a newbilie so my questions might seem very well-- kinda dumb so
>> please
>> forgive me.
>>
>> I'm trying to do the following in Sqlite.
>>
>> TeamTable -
>>     parId: 4
>>     personIDs :  1,5,9,6
>>
>> PersonTable -
>>    id:
>>    name:
>>
>> SELECT * FROM PersonTable WHERE id IN (SELECT personIDs FROM PersonTable
>> WHERE parId = 4);
>>
>> The result from the above query is empty whereas when I do:
>> SELECT * FROM PersonTable WHERE id IN(1,5,9,6)
>>
>> Then I get the result that I need.
>>
>> Any help is greatly appreciated.
>>
>> Cheers.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> 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: [hidden email]
>>
>> 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
>> [hidden email]
>> 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-tp75751p75753.html
>>
>> To unsubscribe from Simple Select from IN - from a newbie., visit
>>
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

Stephan Beal-3
In reply to this post by Humblebee
On Wed, May 21, 2014 at 6:00 PM, Humblebee <[hidden email]> wrote:

> | 1          | 4       | 1,5,2,3,4   |
> | 2          | 5       | 2,6,3,5,1   |
> +----------------------------------|
> ...
> @Simon, thank you for showing me a better way to setup the tables,
>

Without doing what Simon suggests, there is no good solution to your
problem with the data structure you have. SQL is made for normalized data,
not strings containing arbitrary tokens separated by arbitrary other tokens.


> your way makes much more sense,  only problem is that in this
> situation, the tables have already been defined and made by someone
> else so I cannot change it.  I'm a bit stuck with the way it is.
>

Then you're stuck with the problem you've got.


--
----- 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

R Smith
In reply to this post by Humblebee

On 2014/05/21 18:00, Humblebee wrote:

> Thank you for your replies.
>
> I'm sorry for not being super clear about the problem:
>
> Note: The parId belongs to another table not shown here.
>
> TeamTable
> +---------------------------------+
> | id         | parId | personIds |
> +---------------------------------+
> | 1          | 4       | 1,5,2,3,4   |
> | 2          | 5       | 2,6,3,5,1   |
> +----------------------------------|
>
> PersonTable
> +------------------+
> | id         | name |
> +------------------+
> | 4          | 4       |
> | 5          | 5       |
> +---------+-------+
>
> @Simon, thank you for showing me a better way to setup the tables,
> your way makes much more sense,  only problem is that in this
> situation, the tables have already been defined and made by someone
> else so I cannot change it.  I'm a bit stuck with the way it is.

There is no other way sadly, you have to make the data behave. You have some options though depending on some other things like:

1) We know you cannot change the current tables but can you change how they are created?
This might mean you can change the creating or inserting scripts and then just convert the existing tables to the correct format,
which we can tell you easy ways to achieve.

2) You cannot change how the tables are created, but you can add more tables?
This means we can simply add the tables in the way we like them and only need a trigger or such to decompose the data into our newer
tables when the originator inserts them.

3) You cannot add new tables or change the data entry process at all.. you /HAVE/ to just use a query...
Well in that case, we've had the deravelling of csv data on this list before, and I did make some CTE (which I will repost below) to
fix it, but it is slow and you will need to be using a version of SQLite that supports CTE (3.8.4+) - it's ugly but will do the job.

4) If you control the C code that actually executes the query in your own software.. the very best solution would be reading the
next value and decomposing the csv INSIDE your own code, and then adding it to a next query that gets the final results. Problem is
the Query values need to be known at *Prepare* time before any steps, so it will have to be individual queries for each extraction -
so may well be as slow as the CTE solution.

You really cannot however expect to have anything speedy when your tables are not in 1NF format as Simon suggested and you should
strongly recommend this alteration be made to the powers that be.

Anyway, the next bit of CTE (copy-lifted from previous posts) will extract you the csv values, it's contains both the working code
and an example table (maybe try to run the example first) so you should translate it a bit to suit your needs, if you have any
difficulty, please ask  (and it needs recent version SQlite as I mentioned...) Best of luck!

---------------------------------------------------------
CREATE TABLE tmpcsv (
     ID INTEGER PRIMARY KEY,
     colA TEXT,
     colCSV TEXT
);

INSERT INTO tmpcsv (colA, colCSV) VALUES
('foo', '4,66,51,3009,2,678'),
('bar', 'Sputnik,Discovery'),
('baz', '101,I-95,104');


   WITH csvrec(i,l,c,r) AS (
       SELECT tmpcsv.ID, 1, colCSV||',', '' FROM tmpcsv
     UNION ALL
       SELECT i,
              instr(c,',') AS vLen,
              substr(c,instr(c,',')+1) AS vRem,
              substr(c,1,instr(c,',')-1) AS vCSV
       FROM csvrec
       WHERE vLen>0
     )
   SELECT t.ID, t.colA, rt.r FROM tmpcsv AS t, csvrec AS rt
   WHERE t.ID=rt.i AND rt.r<>''
   ORDER BY t.ID
   LIMIT 100

Results:
ID    colA  r
---    ----    ---
1    foo    4
1    foo    66
1    foo    51
1    foo    3009
1    foo    2
1    foo    678
2    bar    Sputnik
2    bar    Discovery
3    baz    101
3    baz    I-95
3    baz    104



Cheers,
Ryan










_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

Andy Goth-3
In reply to this post by Stephan Beal-3
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 5/21/2014 11:09 AM, Stephan Beal wrote:
> On Wed, May 21, 2014 at 6:00 PM, Humblebee
> <[hidden email]> wrote:
>> | 1          | 4       | 1,5,2,3,4   | | 2          | 5       |
>> 2,6,3,5,1   |
>
> Without doing what Simon suggests, there is no good solution to
> your problem with the data structure you have. SQL is made for
> normalized data, not strings containing arbitrary tokens separated
> by arbitrary other tokens.

It's probably been suggested at least once in response to the thousands
of times this same question has come up on the list, but I'll put it
forward again.  Perhaps create virtual tables which are essentially
views onto this table but with a normalized presentation.

Something like:

CREATE TABLE TeamAssignment (
    parId INTEGER NOT NULL REFERENCES TeamTable,
    personId INTEGER NOT NULL REFERENCES PersonTable,
    PRIMARY KEY (parId, personId)
);

If you had this, you'd be able to associate persons with their teams,
including situations where a team is empty and a person is in many
teams.

Going the virtual table route will have a negative impact on performance
and maintainability, compared to actually normalizing your database.
But you say you're stuck, so do what you have to.  And honestly, please
don't give people with no knowledge of SQL theory the power to set your
SQL schema in stone.

- --
Andy Goth | <andrew.m.goth/at/gmail/dot/com>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.22 (MingW32)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJTfNSYAAoJELtYwrrr47Y4GToH/3uXzvBNY499X3nfSEsWPcR4
42o1W/dGRvdwi6p4hGjZmL55RhHwpTjljHaupszF1o3SB6nGlBDaxeOD86SYMATC
KV8w8aNxaFmo6SnwaMmLTOKfL5qnFqqcEV6FD4wpIbSaziteG39AenN4kQqIYAbH
Dpk0XULlrRQClRP+77CWQvdodWzK0C9YEkNCaCYgez6MrvfDOpvTz8s83aKsOSCS
QGGfwHUuwFg96tf1jVYi0PUOiZHofWBXaRESP59uFAO3cRiuOEydHSbabjufexub
hf4ubOsfqlLnJuWhoWXNBzMGDRe2Iu8v46iNR5CT2c4/nzjjkESsKW05xxC86oM=
=c5Bu
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

R Smith
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."

Quote of the day - Probably the single most valuable bit of advice ever!

// I don't do facebook, but if I did, that would go on my wall :)


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

Petite Abeille-2
In reply to this post by Humblebee

On May 21, 2014, at 6:00 PM, Humblebee <[hidden email]> wrote:

> only problem is that in this situation, the tables have already been defined and made by someone
> else so I cannot change it.  I'm a bit stuck with the way it is.

Nah… it’s software… you can always change it… in fact, better fix it now… as there is really no reasonable way forward with your current setup…



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

Simon Slavin-3

On 21 May 2014, at 7:20pm, Petite Abeille <[hidden email]> wrote:

> On May 21, 2014, at 6:00 PM, Humblebee <[hidden email]> wrote:
>
>> only problem is that in this situation, the tables have already been defined and made by someone
>> else so I cannot change it.  I'm a bit stuck with the way it is.
>
> Nah… it’s software… you can always change it… in fact, better fix it now… as there is really no reasonable way forward with your current setup…

If you need the original data intact write a conversion routine you can run at any time.  It should read the 'personIDs' field for each team and use it to write data into a new table.  Then you can use this new table in as many queries as you want.

The code which depends on the existing tables doesn't need to know about the new table so your 'someone else' shouldn't care.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

Jim Dodgen
I fully agree a bad database design can impact you for the life of the
application. If this is a class assignment and the instructor gave you this
as a problem then I can understand "I cannot change it" otherwise fix it
now or pay forever.

*Jim Dodgen*








On Wed, May 21, 2014 at 11:27 AM, Simon Slavin <[hidden email]> wrote:

>
> On 21 May 2014, at 7:20pm, Petite Abeille <[hidden email]>
> wrote:
>
> > On May 21, 2014, at 6:00 PM, Humblebee <[hidden email]> wrote:
> >
> >> only problem is that in this situation, the tables have already been
> defined and made by someone
> >> else so I cannot change it.  I'm a bit stuck with the way it is.
> >
> > Nah… it’s software… you can always change it… in fact, better fix it
> now… as there is really no reasonable way forward with your current setup…
>
> If you need the original data intact write a conversion routine you can
> run at any time.  It should read the 'personIDs' field for each team and
> use it to write data into a new table.  Then you can use this new table in
> as many queries as you want.
>
> The code which depends on the existing tables doesn't need to know about
> the new table so your 'someone else' shouldn't care.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

Stephan Beal-3
On Wed, May 21, 2014 at 8:45 PM, Jim Dodgen <[hidden email]> wrote:

> I fully agree a bad database design can impact you for the life of the
> application. If this is a class assignment and the instructor gave you this
> as a problem then I can understand "I cannot change it" otherwise fix it
> now or pay forever.
>

If it IS a class assignment, the professor's point will almost certainly be
along the lines of, "see how difficult that was? Now let's see how much
easier it can be done..." (at which point he introduces normalization).


--
----- 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

Petite Abeille-2
In reply to this post by Humblebee

On May 21, 2014, at 6:00 PM, Humblebee <[hidden email]> wrote:

> At least this is what I'm thinking from my very very limited understanding of SQL and with the way that I'm trying to do this.

SMITH: Doctor, it hurts when I do _this_.
DALE: Don’t _do_ that.


with
DataSet
as
(
  select 'a,b,c,' as string
),
CSV( string, value, position )
as
(
  select  substr( string, instr( string, ',' ) + 1 ) as string,
          substr( string, 1, instr( string, ',' ) - 1 ) as value,
          1 as position
  from    DataSet

  union all
  select  substr( string, instr( string, ',' ) + 1 ) as string,
          substr( string, 1, instr( string, ',' ) - 1 ) as value,
          position + 1 as position
  from    CSV
  where   length( string ) > 0
)
select  value,
        position
from    CSV;

> value|position
> a|1
> b|2
> c|3

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

Keith Medcalf
In reply to this post by Andy Goth-3
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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]
<[hidden email]> 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
> [hidden email]
> 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
>
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

Hick Gunter
No. The internal table stores only unique keys.

-----Ursprüngliche Nachricht-----
Von: Humblebee [mailto:[hidden email]]
Gesendet: Donnerstag, 22. Mai 2014 11:39
An: [hidden email]
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]
<[hidden email]> 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
> [hidden email]
> 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
[hidden email]
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: [hidden email]

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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]
<[hidden email]> wrote:

>
>
> No. The internal table stores only unique keys.
>
> -----Ursprüngliche Nachricht-----
> Von: Humblebee [mailto:[hidden email]]
> Gesendet: Donnerstag, 22. Mai 2014 11:39
> An: [hidden email]
> 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]
> <[hidden email]> 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
>> [hidden email]
>> 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
> [hidden email]
> 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: [hidden email]
>
> 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
> [hidden email]
> 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
>
Reply | Threaded
Open this post in threaded view
|

Re: Simple Select from IN - from a newbie.

R Smith
In reply to this post by Humblebee

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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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] <[hidden email]> 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 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
> [hidden email]
> 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-tp75751p75773.html
>
> To unsubscribe from Simple Select from IN - from a newbie., visit
>
123