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

On Mon, May 26, 2014 at 10:33 PM, Humblebee wrote: > Thank you everyone for your advice. > > I will definitely try to find a good course to strengthen my core > knowledge of SQL. Thanks everyone for helping me to get started. I > hope that I wasn't being a burden. I

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

Thank you everyone for your advice. I will definitely try to find a good course to strengthen my core knowledge of SQL. Thanks everyone for helping me to get started. I hope that I wasn't being a burden. I did state that I was a complete Newbie and my questions were going to be dumb. So

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

On 25 May 2014, at 9:23pm, RSmith wrote: > On the plus side, they are mostly free and even the most expensive ones > doesn't come near what Stephen paid (All I can say to him is: Dude, you wuz > robbed). The rise of the internet has changed the way people learn computer

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

On 2014/05/25 19:37, Humblebee wrote: Thanks for the tip about the online course with an instructor. I will look into online courses, although the 5 digits is a bit outside of my limited budget. Learning is generally a very personal thing and there are many ways to take in information. I

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

Thanks for the tip about the online course with an instructor. I will look into online courses, although the 5 digits is a bit outside of my limited budget. Learning is generally a very personal thing and there are many ways to take in information. I have to say that I learned so much from

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

@OP: This isn't meant as something to be meant as mean, but I'd recommend taking an online course for SQL in general. There are some free sites, there are a lot more paid sites. I did my SQL training back when SQL 2000 was just coming out, and I paid 5 digits for the course. Those five digits

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

On Sat, 24 May 2014 22:24:01 +0200, RSmith wrote: > INSERT OR UPDATE TeamPersonTable (tpId,teamId,personId,orderId) > VALUES (1,3,4,0),(2,3,5,1),(3,7,4,1),(4,7,5,0); That will raise a syntax error. Make it: INSERT OR REPLACE INTO TeamPersonTable

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

Thank you everyone for your help and especially for the very detailed and clear explanation. There are so many intricacies of SQL that it's hard to know what to do as a beginner. Having such clear and understandable explanations makes learning the ropes so much easier. I am supplementing my

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

On 2014/05/24 12:08, Humblebee wrote: I'm making good headway with the conversion from the use of strings into a normalized database. With the kind help of everyone on the list, I can retrieve the data from the new structure. I am now at the stage of saving the data to the new structure.

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

On 24 May 2014, at 11:08am, Humblebee wrote: > I looked up the SQLite > documentation and found that Insert works on multiple rows. Can I > update multiple rows with one statement ? No. But if you do not already understand BEGIN and END you should read about them.

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

On Sat, 24 May 2014 03:08:32 -0700 (PDT), Humblebee wrote: >I'm making good headway with the conversion from the use of strings >into a normalized database. With the kind help of everyone on the >list, I can retrieve the data from the new structure. > >I am now at the

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

I'm making good headway with the conversion from the use of strings into a normalized database. With the kind help of everyone on the list, I can retrieve the data from the new structure. I am now at the stage of saving the data to the new structure. Each time I do an update, I need to update

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

I had the misconception that a View contains stored data - come to think of it, it would not be practical if the result set was really large. Thank you everyone for giving me a better understanding of what a view really is about. Cheers. On 5/24/14, Simon Slavin-3 [via SQLite]

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

On 23 May 2014, at 7:53pm, Humblebee wrote: > I have a question: "so every time you run a query against a view, > that view's query is run/updated if not cached ". > > Does this mean that if the View is Temporary, then it's not cached? > and for normal views, it's

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

On 2014/05/23 20:54, Stephan Beal wrote: On Fri, May 23, 2014 at 8:53 PM, Humblebee wrote: Does this mean that if the View is Temporary, then it's not cached? and for normal views, it's cached? No - a TEMP VIEW means the view is automatically destroyed when you

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

On Fri, May 23, 2014 at 8:53 PM, Humblebee wrote: > Does this mean that if the View is Temporary, then it's not cached? > and for normal views, it's cached? > No - a TEMP VIEW means the view is automatically destroyed when you close the db connection, and that view is

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

Many Thanks Ryan. I'm learning so much in the last 2 days (Normalization,Grouping,CTEs, Views,..) I have a question: "so every time you run a query against a view, that view's query is run/updated if not cached ". Does this mean that if the View is Temporary, then it's not cached? and for

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

On Fri, May 23, 2014 at 8:33 PM, Stephan Beal wrote: > FWIW, you can also use [table] instead of "table". > With, apparently, some corner-cases: sqlite> create table [t] (a,b,c); sqlite> insert into [t] values(1,2,3); But... sqlite> .dump [t] PRAGMA foreign_keys=OFF;

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

On Fri, May 23, 2014 at 8:31 PM, Andy Goth wrote: > CREATE TABLE "table" (id INTEGER PRIMARY KEY, a, b, c); > "table" in the KEY of C won't cause any confusion in the context of music, will it ;) > So that's what double quotes means. Single quotes, on the other

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

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 5/22/2014 5:23 PM, RSmith wrote: > On 2014/05/22 17:25, Humblebee wrote: >> 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

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

On 2014/05/23 04:48, Humblebee wrote: 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

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

CTEs seems like another magic ball. I just read up a little bit about them. The bag of tricks is getting more colorful. I would love to use this new feature, sadly the version of Sqlite is 3.7.7.1, you mentioned views, is this something that I can use for the version of Sqlite that I'm on.

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

On 2014/05/22 17:25, Humblebee wrote: Oops back sooner than I thought. I would like to order the grouping of the Persons in a particular order. So the sequence of the result is based on the TeamPersonTable's order field. First a word of warning - Please do not use column names that are the

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

Oops back sooner than I thought. I would like to order the grouping of the Persons in a particular order. So the sequence of the result is based on the TeamPersonTable's order field. TeamTable +---+ | id | name | +---+ | 1 | blue| | 2

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

RSmith, Two thumbs up for your super kind help. I will study the queries that you gave and try them backwards and forwards to better understand the magic. Hope you don't mind if I ask more newbie questions in the future. Cheers to the many nice people in this Great list. On 5/22/14, fantasia

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

I'm very happy to have taken the advice of the Awesome people on this list to add an additional table and make the data normalized. From today, I learned that the word Normalized -> Magic. On 5/22/14, fantasia dosa wrote: > Ah yes, you are correct. > > It was my typo.

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

Ah yes, you are correct. It was my typo. Works perfectly. On 5/22/14, RSmith [via SQLite] wrote: > > > I'm trying to understand the grouping magic and testing the first and > last query. > Only thing is for some reason, I'm not getting any result for the

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

I'm trying to understand the grouping magic and testing the first and last query. Only thing is for some reason, I'm not getting any result for the last query. Perhaps i'm doing something wrong. Works perfectly for me, are you sure you have it exactly so? Check the table names correspond to

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

I'm trying to understand the grouping magic and testing the first and last query. Only thing is for some reason, I'm not getting any result for the last query. Perhaps i'm doing something wrong. On 5/22/14, RSmith [via SQLite] wrote: > > > > On 2014/05/22

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

On 2014/05/22 14:28, Humblebee wrote: Thanks for your answers. Wow, I just tried the "Group" thing and it's truly magic. Big Bang ! The first query gave the Exact answer I was looking for. It even assembled a string. Amazing how this works. I agree :) Is the last query the equivalent of

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

Thanks for your answers. Wow, I just tried the "Group" thing and it's truly magic. Big Bang ! The first query gave the Exact answer I was looking for. It even assembled a string. Amazing how this works. Is the last query the equivalent of the first query? On 5/22/14, RSmith [via SQLite]

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

On 2014/05/22 13:39, Humblebee wrote: TeamTable +---+ | id | name | +---+ | 1 | blue| | 2 | green | +---| PersonTable +--+ | id | name | +--+ | 4 | john | | 5

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

select p.id,p.name from TeamPersonTable tp join PersonTable p on (p.id = tp.personId) where tp.teamId = 1; -Ursprüngliche Nachricht- Von: Humblebee [mailto:fantasia.d...@gmail.com] Gesendet: Donnerstag, 22. Mai 2014 13:40 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Simple Select

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

TeamTable +---+ | id | name | +---+ | 1 | blue| | 2 | green | +---| PersonTable +--+ | id | name | +--+ | 4 | john | | 5 | bill | +-+---+

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

On 2014/05/22 13:02, Humblebee wrote: @RSmith, Very much appreciate you taking the time to write such a detailed and awesome explanation of how the string and list works in SQL. I had no idea what goes on there. It is my pleasure, sadly I know exactly how it feels to be under the wrong

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

To be more clear. So now I have 3 tables. Person Team Person_Team Query: 1. Get all persons that belongs to a certain team. The output should be: persons = "name1,name2,name3" personIds = "id1,id2,id3" So in other words, each field is a string delimited by a comma. On 5/22/14, fantasia

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

@RSmith, Very much appreciate you taking the time to write such a detailed and awesome explanation of how the string and list works in SQL. I had no idea what goes on there. I also liked the car-jacking example, very funny - that made the entire thing very clear. You're right, I wouldn't

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

On 2014/05/22 11:39, Humblebee wrote: Thank you everyone for your kind input and suggestions. That is quite a lot to consider. I didn't realize it would be so difficult for a Select statement to return a string. Out of curiosity, when I do: SELECT * FROM Person WHERE id IN(2,2,3,3) It

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

ode+s1065341n75770...@n5.nabble.com> 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: [sql

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

No. The internal table stores only unique keys. -Ursprüngliche Nachricht- Von: Humblebee [mailto:fantasia.d...@gmail.com] Gesendet: Donnerstag, 22. Mai 2014 11:39 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Simple Select from IN - from a newbie. Thank you everyone for your kind

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.

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

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

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

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,

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

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 (

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

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 >

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

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

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

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,

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

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 :)

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

-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,

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

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 |

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

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

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

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

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

chricht- > 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 se

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

. 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

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

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

[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