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

[Keith Medcalf]

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

Re: [sqlite] My first hot journal, what do I do with it?

[Simon Slavin]

On 21 May 2014, at 9:16pm, Drago, William @ MWG - NARDAEAST 
 wrote:

> Well, it finally happened. I had a program crash and was left with a hot 
> journal file. A while back there was a post by Richard Hipp detailing 
> something that could/should be done before trying to use the database to 
> assure that SQLite does the rollback. I can't find that post now.

Nothing.  Leave everything as it is.  Do not move or rename the journal file.  
Just run whatever app it was that opens the database again.  SQLite will 
automatically notice the hot journal file and do its best to restore the 
database to an uncorrupted between-transaction state.

If you want to be extremely cautious take a backup copy of the database and the 
journal file before you reopen the database file.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] My first hot journal, what do I do with it?

[Drago, William @ MWG - NARDAEAST]

All,

Well, it finally happened. I had a program crash and was left with a hot 
journal file. A while back there was a post by Richard Hipp detailing something 
that could/should be done before trying to use the database to assure that 
SQLite does the rollback. I can't find that post now.

Any suggestions?

Thanks,
--
Bill Drago
Senior Engineer

L3 Communications / Narda Microwave East
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
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.

[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  wrote:

>
> On 21 May 2014, at 7:20pm, Petite Abeille 
> wrote:
>
> > On May 21, 2014, at 6:00 PM, Humblebee  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
> 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.

[Petite Abeille]

On May 21, 2014, at 6:00 PM, Humblebee  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
  fromDataSet

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

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

___
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.

[Stephan Beal]

On Wed, May 21, 2014 at 8:45 PM, Jim Dodgen  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 21 May 2014, at 7:20pm, Petite Abeille  wrote:

> On May 21, 2014, at 6:00 PM, Humblebee  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Petite Abeille]

On May 21, 2014, at 6:00 PM, Humblebee  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[RSmith]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Andy Goth]

-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
>  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 | 
-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
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/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:
IDcolA  r
------
1foo4
1foo66
1foo51
1foo3009
1foo2
1foo678
2barSputnik
2barDiscovery
3baz101
3bazI-95
3baz104



Cheers,
Ryan










___
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.

[Stephan Beal]

On Wed, May 21, 2014 at 6:00 PM, Humblebee  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Humblebee]

Sorry, clicked the send button too quickly.

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

On 5/21/14, fantasia dosa  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]
>  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:fantasia.d...@gmail.com]
>> Gesendet: Mittwoch, 21. Mai 2014 15:00
>> An: sqlite-users@sqlite.org
>> 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
>> 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-tp75751p75753.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-tp75751p75755.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]

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]
 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:fantasia.d...@gmail.com]
> Gesendet: Mittwoch, 21. Mai 2014 15:00
> An: sqlite-users@sqlite.org
> 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
> 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-tp75751p75753.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-tp75751p75754.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.

[Hick Gunter]

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:fantasia.d...@gmail.com]
Gesendet: Mittwoch, 21. Mai 2014 15:00
An: sqlite-users@sqlite.org
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
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.

[Simon Slavin]

On 21 May 2014, at 1:59pm, fantasia dosa  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[fantasia dosa]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Database Browser officially moved to GitHub

[Simon Slavin]

On 21 May 2014, at 9:36am, Tim Streater  wrote:

> On 21 May 2014 at 00:17, RSmith  wrote: 
> 
>> On 2014/05/20 23:37, Tim Streater wrote:
>>> Sorry, should have said I'm on OS X Mavericks 10.9.3.
>> 
>> We know... no other OS would report CPU usage as 400%... :)
> 
> :-)
> 
> Well, to be fair, I don't know what OS X was reporting.

For those not in on the joke, a fully-loaded four-core Macintosh can report 
that it is working at 400% capacity.  This allows you to compare efficiency 
with other Macs which have different numbers of cores without having to double 
or halve numbers.  But it does look weird when you see CPU > 100%.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Database Browser officially moved to GitHub

[justin]

On 2014-05-21 08:36, Tim Streater wrote:


Of course, it might be lying in its teeth for all I know, but the fan
did wind up.


Nah, it wasn't lying.  Qt is a huge amount of code,
and Homebrew uses all available processor cores to
compile it.  And it still take ages. ;)

+ Justin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Database Browser officially moved to GitHub

[Tim Streater]

On 21 May 2014 at 00:17, RSmith  wrote: 

> On 2014/05/20 23:37, Tim Streater wrote:
>> Sorry, should have said I'm on OS X Mavericks 10.9.3.
>
> We know... no other OS would report CPU usage as 400%... :)

:-)

Well, to be fair, I don't know what OS X was reporting. I have iStat Menus 
installed, which shoves a lot of useful stuff in the menu bar. This includes 
thermometers for the four cores, as well as sensor info (fan speed and CPU temp 
in this case).

Of course, it might be lying in its teeth for all I know, but the fan did wind 
up.

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Database Browser officially moved to GitHub

[Rene Peinthor]

As compiling under windows is really a pain I try to avoid it as much as
possible :)

But it should be doable with qmake or cmake with a little tweaking.
But needed libs are Qt and sqlite.

Before I switched to cross-compiling I had an msys environment, but with
some effort it should also work with VS.

Cheers
Rene


On Tue, May 20, 2014 at 7:25 PM,  wrote:

> On 2014-05-20 16:35, big stone wrote:
>
>> Hi Justin,
>>
>> What are the necessary steps to rebuild SQLite Database Browser from
>> source, on a windows 7 PC ?
>>
>> (I have no Qt experience, so not sure of what to download )
>>
>
> Good question.  We should write up some instructions for that. :)
>
> Rene or Martin (both CC'd) are the C++ guys, so may be able to
> help.
>
> I *think* Rene is generating the Windows binaries by cross
> compiling from Linux.  The binaries seem to work pretty well,
> so (thus far) it's been an ok approach. ;)
>
> I tried quickly myself about 2 weeks ago, but wasn't sure which
> bits of what to download (eg msys? mingwin? sqlite-libs?) so
> didn't have any success.  Kept with OSX Homebrew instead at
> the time. ;)
>
> Anyway, hopefully Rene or Martin can help. :)
>
> Regards and best wishes,
>
> Justin Clift
>



-- 
Rene Peinthor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users