Many Thanks Ryan. I'm learning so much in the last 2 days (Normalization,Grouping,CTEs, Views,..)
I have a question: "so every time you run a query against a view, that view's query is run/updated if not cached ". Does this mean that if the View is Temporary, then it's not cached? and for normal views, it's cached? On 5/23/14, RSmith [via SQLite] <ml-node+s1065341n75807...@n5.nabble.com> wrote: > > > > On 2014/05/23 04:48, Humblebee wrote: >> 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. >> > > No problem, of course it can be used with Views - here is an adaption of the > previews using views in stead: > > > Step 1 - Make a view: > > CREATE VIEW TeamPlayersOrdered AS > SELECT T.id AS teamId, T.name AS teamName, P.id AS playerId, P.name AS > playerName > FROM TeamPersonTable TP > LEFT JOIN TeamTable T ON T.id=TP.teamId > LEFT JOIN PersonTable P ON P.id=TP.personId > WHERE 1 > ORDER BY TP.orderId; > -- Creates a View with distinct Fieldnames and everything ordered correctly > > > After having created the View, Step 2 - Do the Queries using the View: > > SELECT group_concat(playerId,','), group_concat(playerName,',') > FROM TeamPlayersOrdered > WHERE teamId=1 > GROUP BY teamId; > -- Shows Players in Order by selected Team ID > > > SELECT playerId, playerName > FROM TeamPlayersOrdered > WHERE teamId=1; > -- Shows Players in Order by selected Team ID as a list > > > SELECT group_concat(teamId,','), group_concat(teamName,',') > FROM TeamPlayersOrdered > WHERE playerId=4 > GROUP BY playerId; > -- Show Teams in Order by selected Player ID > > > The view will persist forever, so you only need to create it once. > If you do NOT want it to last forever, but want to make a view every time > you start a new connection, you can use "CREATE TEMPORARY > VIEW..." which will see the view only persist for the life of the > connection. > > You can make any kind of View from any kind of SELECT query as above, and > then use it just like any normal table with one > consideration, a view is NOT a Table, so every time you run a query against > a view, that view's query is run/updated if not cached > etc. So if the View is based on a really slow Query, then any query using > the view will be slow too - even if it is a simple select. > > > Hope that solves your problem! > Ryan > > _______________________________________________ > 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-tp75751p75807.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-tp75751p75812.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
