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] <ml-node+s1065341n7578...@n5.nabble.com> wrote: > > > > On 2014/05/22 13:39, Humblebee wrote: >> 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" >> > > Ok that one is easy I think - Joins and Grouping are magic - try this: > > SELECT group_concat(P.id,','), group_concat(P.name,',') > FROM TeamPersonTable TP > LEFT JOIN PersonTable P ON P.id=TP.personId > WHERE TP.teamid=1 > GROUP BY TP.teamid; > > > Similarly, if you fancy to pick teams by names, something like this would > work: > > SELECT group_concat(P.id,','), group_concat(P.name,',') > FROM TeamPersonTable TP > LEFT JOIN PersonTable P ON P.id=TP.personId > LEFT JOIN TeamTable T ON T.id=TP.teamId > WHERE T.name LIKE 'blu%' > GROUP BY TP.teamid; > > > Or to see the teams to which a person belongs: > > SELECT group_concat(T.id,','), group_concat(T.name,',') > FROM TeamPersonTable TP > LEFT JOIN PersonTable P ON P.id=TP.personId > LEFT JOIN TeamTable T ON T.id=TP.teamId > WHERE P.name LIKE 'john%' > GROUP BY TP.personId; > > > And if you drop all the words with "group" in them, it will look like a > standard listed query: > > SELECT T.id AS TeamID, T.name AS TeamName, P.id AS PersonID, P.name AS > PersonName > FROM TeamPersonTable TP > LEFT JOIN PersonTable P ON P.id=TP.personId > LEFT JOIN TeamTable T ON T.id=TP.teamId > WHERE P.id=5; > > > Let us know if anything is unclear or you need it to work differently. > (Btw, WHERE P.id=5 will be much faster than WHERE P.name LIKE 'bill%' or > some such) > > _______________________________________________ > 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-tp75751p75782.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-tp75751p75784.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
