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 from IN - from a newbie. TeamTable +-------------------+ | id | name | +-------------------+ | 1 | blue | | 2 | green | +-------------------| PersonTable +------------------+ | id | name | +------------------+ | 4 | john | | 5 | bill | +---------+-------+ TeamPersonTable +-----------------------+ | teamId | personId | +-----------------------+ | 1 | 4 | | 1 | 5 | | 2 | 4 | | 2 | 5 | +---------+----------+ So a person can belong to any teams. Query Input: teamId = 1 Result: personNames = "john,bill" personIds = "4,5" On 5/22/14, RSmith [via SQLite] <ml-node+s1065341n75776...@n5.nabble.com> wrote: > > > > 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 impression (I have been so on this very list!). > >> 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. > > That is great news- I promise you will not be sorry. As for the > question, once you decided how the tables will look, could you again > paste them here with a little bit of example data in each and then say > exactly how you want the resulting data to look after the query is run > - and we will try suggest the most optimized ways of achieving it. A > bit of knowledge about how often the tables will be updated/inserted > to, and how often the query will be run, and how much every table is > expected to grow over time, would all help to make the best decisions. > > > _______________________________________________ > 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-newbi > e-tp75751p75776.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-tp75751p75778.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 ----------------------------------------------------------------------- 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
