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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
