@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] <ml-node+s1065341n75773...@n5.nabble.com> 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 > 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-tp75751p75773.html > > To unsubscribe from Simple Select from IN - from a newbie., visit > http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=75751&code=ZmFudGFzaWEuZG9zYUBnbWFpbC5jb218NzU3NTF8MTk4Njk4NTgwNw== -- View this message in context: http://sqlite.1065341.n5.nabble.com/Simple-Select-from-IN-from-a-newbie-tp75751p75774.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
