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. Many Thanks. On 5/23/14, RSmith [via SQLite] <ml-node+s1065341n75802...@n5.nabble.com> wrote: > > > > 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 same > as SQL keywords, such as "Order"... I took the liberty of > renaming the new column to "orderId" because of this. > > Next, introducing a great new feature of SQLite (and most other SQL Engines) > called CTE or Common Table Expression, which lets you > define one kind of table and then extract a query from it, all in the same > query. (This can also be achieved with Views if the SQL > Engine doesn't support CTE, such as older SQLites, but this is usually a > 2-step process which may or may not be more useful > depending if you use the same base query for other purposes too). > > Here it is simply used to make up a sorted table, and then from it do the > grouping and concat that is needed. > > Same set of Queries as before, but with ordering added - which again is made > possible by that 1NF layout. > > Per Team Query: > > WITH WTP(teamId,personIDs,personNames) AS ( > SELECT TP.teamId, P.id, P.name > FROM TeamPersonTable TP > LEFT JOIN PersonTable P ON P.id=TP.personId > WHERE TP.teamid=1 > ORDER BY TP.orderId > ) > SELECT group_concat(personIDs,','), group_concat(personNames,',') > FROM WTP > GROUP BY WTP.teamId; > > > Per person Query: > > WITH WTP(personId,teamIDs,teamNames) AS ( > SELECT P.id, T.id, T.name > FROM TeamPersonTable TP > LEFT JOIN TeamTable T ON T.id=TP.teamId > LEFT JOIN PersonTable P ON P.id=TP.personId > WHERE P.id=5 > ORDER BY TP.orderId > ) > SELECT group_concat(teamIDs,','), group_concat(teamNames,',') > FROM WTP > GROUP BY WTP.personId; > > > Again, you can just remove all the grouping words to reveal normal Query > listings. > > >> TeamTable >> +-------------------+ >> | id | name | >> +-------------------+ >> | 1 | blue | >> | 2 | green | >> +-------------------| >> >> >> PersonTable >> +------------------+ >> | id | name | >> +------------------+ >> | 4 | john | >> | 5 | bill | >> +---------+-------+ >> >> TeamPersonTable >> +-----------------------------------+ >> | teamId | personId | orderId >> +----------------------------------+ >> | 1 | 4 | 1 >> | 1 | 5 | 0 >> | 2 | 4 | 0 >> | 2 | 5 | 1 >> +---------+------------+----------+ >> >> >> >> Query Input: teamId = 1 >> >> Result: >> >> personNames = "bill,john" >> personIds = "5,4" >> >> ------------- >> >> Query Input: teamId = 2 >> >> Result: >> >> personNames = "john,bill" >> personIds = "4,5" >> > > > _______________________________________________ > 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-tp75751p75802.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-tp75751p75804.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
