To be more clear. So now I have 3 tables.
Person Team Person_Team Query: 1. Get all persons that belongs to a certain team. The output should be: persons = "name1,name2,name3" personIds = "id1,id2,id3" So in other words, each field is a string delimited by a comma. On 5/22/14, fantasia dosa <[email protected]> wrote: > @RSmith, > > Very much appreciate you taking the time to write such a detailed and > awesome explanation of how the string and list works in SQL. I had no > idea what goes on there. > > I also liked the car-jacking example, very funny - that made the > entire thing very clear. You're right, I wouldn't expect 4 cars out > of the same green color. > > As for now, I'm following the good advice of all the much more > knowledgeable people on this list to normalize the data by adding a > Person_Team table to the database. My last question is what SQL > statement could I use to get the information out of these 3 tables. > > On 5/22/14, RSmith [via SQLite] <[email protected]> > 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 >> [email protected] >> 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-tp75751p75775.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
