[sqlite] CTE for a noob
On 2016/03/09 10:01 PM, David Raymond wrote: > For my own clarification, the statements quoted way down below aren't exactly > equivalent, correct? > > "For each pair of columns identified by a USING clause, the column from the > right-hand dataset is omitted from the joined dataset. This is the only > difference between a USING clause and its equivalent ON constraint." > > (Short version: USING will return fewer fields than ON, and order can matter > for both types because of affinity/collation) Indeed so - the USING optimises the columns out that are coincidental (by assuming duplication would be unwanted) when you request the asterisk column wildcard, as I did in the examples. I should have been more specific in that it is the result-set that is algebraically equivalent. This per-chance column layout should however never be depended on, always specify the columns that you want in the results in any production query. Thanks, good to point this out to prospective USING users! :) Cheers, Ryan
[sqlite] CTE for a noob
For my own clarification, the statements quoted way down below aren't exactly equivalent, correct? "For each pair of columns identified by a USING clause, the column from the right-hand dataset is omitted from the joined dataset. This is the only difference between a USING clause and its equivalent ON constraint." (Short version: USING will return fewer fields than ON, and order can matter for both types because of affinity/collation) So if you have tables tA and tB (ID integer, FirstName text COLLATE NOCASE, LastName text COLLATE NOCASE)... with contents tA: (1, 'john', 'smith') tB: (2, 'JOHN', 'SMIth') then select * from tA inner join tB USING (FirstName, LastName); gives 1, 'john', 'smith', 2 select * from tB inner join tA USING (FirstName, LastName); gives 2, 'JOHN', 'SMIth', 1 and select * from tA inner join tB on tA.Firstname = tB.FirstName and tA.LastName = tB.LastName; gives 1, 'john', 'smith', 2, 'JOHN', 'SMIth' And let's say tB did NOT have the COLLATE NOCASE, then select * from tA inner join tB USING (FirstName, LastName); would use tA's NOCASE collation (first table listed) for all fields and return the 1 record, whereas select * from tA inner join tB ON tB.FirstName = tA.FirstName and tB.LastName = tA.LastName; would use tB's BINARY collation (tB on the left side of the =) and return no records. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Tuesday, March 08, 2016 12:11 PM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] CTE for a noob On 2016/03/08 5:02 AM, Stephen Chrzanowski wrote: > Now I'll have to use USING a bit more often to get the drift and get > out of this multi-call thing. I sort of see what is going on here, > but practice is whats needed. "USING" has three uses in SQLite, first to enlist a virtual table, secondly to suggest an Index to the Query Planner and thirdly as in the example Igor gave where "USING" is simply short-hand for a join where the joining index fields are simple and named the same. This is defined in the standard as a join operation, by the way, works everywhere so not special to SQLite. Easiest is probably by dual example - these two statements are equivalent: SELECT * FROM tA JOIN tB USING (ID) vs. SELECT * FROM tA JOIN tB ON tB.ID = tA.ID Of course this next query can't be simplified since the field-names do not match: SELECT * FROM tA JOIN tB ON tB.ParentID = tA.ID which might make the "USING" thing seem a bit overrated at first glance, but consider the following equivalent queries to see its simplifying power: SELECT * FROM tA JOIN tB ON tB.Surname = tA.Surname AND tB.FirstName = tA.FirstName AND tB.DateOfBirth = tA.DateOfBirth vs. SELECT * FROM tA JOIN tB USING (Surname, FirstName, DateOfbirth) the basic format of which, I might add, covers a very large percentage of typical joined queries. Cheers! Ryan ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CTE for a noob
On 2016/03/08 5:02 AM, Stephen Chrzanowski wrote: > Now I'll have to use USING a bit more often to get the drift and get out of > this multi-call thing. I sort of see what is going on here, but practice > is whats needed. "USING" has three uses in SQLite, first to enlist a virtual table, secondly to suggest an Index to the Query Planner and thirdly as in the example Igor gave where "USING" is simply short-hand for a join where the joining index fields are simple and named the same. This is defined in the standard as a join operation, by the way, works everywhere so not special to SQLite. Easiest is probably by dual example - these two statements are equivalent: SELECT * FROM tA JOIN tB USING (ID) vs. SELECT * FROM tA JOIN tB ON tB.ID = tA.ID Of course this next query can't be simplified since the field-names do not match: SELECT * FROM tA JOIN tB ON tB.ParentID = tA.ID which might make the "USING" thing seem a bit overrated at first glance, but consider the following equivalent queries to see its simplifying power: SELECT * FROM tA JOIN tB ON tB.Surname = tA.Surname AND tB.FirstName = tA.FirstName AND tB.DateOfBirth = tA.DateOfBirth vs. SELECT * FROM tA JOIN tB USING (Surname, FirstName, DateOfbirth) the basic format of which, I might add, covers a very large percentage of typical joined queries. Cheers! Ryan
[sqlite] CTE for a noob
Have you seen this tutorial? https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/ -- Bill Drago Staff Engineer L3 Narda-MITEQ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / William.Drago at L-3COM.com > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski > Sent: Monday, March 07, 2016 9:47 PM > To: General Discussion of SQLite Database > Subject: [sqlite] CTE for a noob > > I'd like to learn how to use CTEs by example when working on my own > project, by mutating it from individual calls into one clean call. I looked > at the > page (As I said in my previous email/note/post(?)) and just thought I'd put > this question out there. > > Given I have the schema posted here: http://pastebin.com/hA6weV4n > > Currently my application makes two queries to the database. One to get the > list of projects via [ select ProjectID,Description from Projects order by > Description ] > > It then goes through each record retrieved and then gets another query via [ > select (select count(VideoID) from vViewedVideos where DateViewed is null > and ProjectID=:P) Unwatched, (select count(VideoID) from vViewedVideos > where ProjectID=:P) Videos ] where :P is the ProjectID. > > I then take the results of ProjectID, Description, Unwatched and Watched > and format a string to put it into a listbox on the form. > > I want to change the order in which this listbox is populated based on a > single > query and the final sort order of [ order by Unwatched=0, Unwatched ] so > that anything that has no unwatched videos available are at the bottom of > list, and any videos that have something to watch are sorted ascending at the > top of the list, 1 to whatever Maybe even add the flexibility later on down > the line so that I can change the order based on [ order by Description ] or [ > Unwatched=0, Description ] or whatever, just by changing the final Order By > clause at runtime. > > From what magic I've seen on the CTE page at sqlite.org, this should be easy > to do, but I've not a clue where to start. > > If you'd like to look at the raw database, it is found here: > https://dl.dropboxusercontent.com/u/1598459/sql/videos.db3 > > Any hints and thoughts would be appreciated! > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments.
[sqlite] CTE for a noob
Awesome... And no CTE. General over complications and over thinking and tunnel-visioned on my part. I just had to tag on whatever sort order I want, and voila. Appreciated. Now I'll have to use USING a bit more often to get the drift and get out of this multi-call thing. I sort of see what is going on here, but practice is whats needed. Thanks again! On Mon, Mar 7, 2016 at 9:56 PM, Igor Tandetnik wrote: > On 3/7/2016 9:47 PM, Stephen Chrzanowski wrote: > >> Currently my application makes two queries to the database. One to get >> the >> list of projects via [ select ProjectID,Description from Projects order by >> Description ] >> >> It then goes through each record retrieved and then gets another query via >> [ select (select count(VideoID) from vViewedVideos where DateViewed is >> null >> and ProjectID=:P) Unwatched, (select count(VideoID) from vViewedVideos >> where ProjectID=:P) Videos ] where :P is the ProjectID. >> > > > select ProjectID, Description, > sum(VideoID is not null and DateViewed is null) Unwatched, > count(VideoID) Videos > from Projects left join vViewedVideos using (ProjectID) > group by ProjectID; > > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] CTE for a noob
On 3/7/2016 9:47 PM, Stephen Chrzanowski wrote: > Currently my application makes two queries to the database. One to get the > list of projects via [ select ProjectID,Description from Projects order by > Description ] > > It then goes through each record retrieved and then gets another query via > [ select (select count(VideoID) from vViewedVideos where DateViewed is null > and ProjectID=:P) Unwatched, (select count(VideoID) from vViewedVideos > where ProjectID=:P) Videos ] where :P is the ProjectID. select ProjectID, Description, sum(VideoID is not null and DateViewed is null) Unwatched, count(VideoID) Videos from Projects left join vViewedVideos using (ProjectID) group by ProjectID; -- Igor Tandetnik
[sqlite] CTE for a noob
I'd like to learn how to use CTEs by example when working on my own project, by mutating it from individual calls into one clean call. I looked at the page (As I said in my previous email/note/post(?)) and just thought I'd put this question out there. Given I have the schema posted here: http://pastebin.com/hA6weV4n Currently my application makes two queries to the database. One to get the list of projects via [ select ProjectID,Description from Projects order by Description ] It then goes through each record retrieved and then gets another query via [ select (select count(VideoID) from vViewedVideos where DateViewed is null and ProjectID=:P) Unwatched, (select count(VideoID) from vViewedVideos where ProjectID=:P) Videos ] where :P is the ProjectID. I then take the results of ProjectID, Description, Unwatched and Watched and format a string to put it into a listbox on the form. I want to change the order in which this listbox is populated based on a single query and the final sort order of [ order by Unwatched=0, Unwatched ] so that anything that has no unwatched videos available are at the bottom of list, and any videos that have something to watch are sorted ascending at the top of the list, 1 to whatever Maybe even add the flexibility later on down the line so that I can change the order based on [ order by Description ] or [ Unwatched=0, Description ] or whatever, just by changing the final Order By clause at runtime. >From what magic I've seen on the CTE page at sqlite.org, this should be easy to do, but I've not a clue where to start. If you'd like to look at the raw database, it is found here: https://dl.dropboxusercontent.com/u/1598459/sql/videos.db3 Any hints and thoughts would be appreciated!
