Rich, Keith, Luuk, Thank you all for taking the time to write such cogent and helpful replies.
I?ve tried out the ideas and they all work fine. We?ve also been looking at the design of our database and our logic to see if thats right. As I mentioned previously, I have feeling that this issue is possibly due to bad DB design on our part so we need to look at that carefully. I?m rather nervous of propagating bad design any further so we?ll check carefully. Either way we have a working solution, so I feel optimistic. Thanks again and as its Saturday have a drink. If any of you are near York in England I?ll happily buy you a pint. Best wishes, Rob. > On 23 May 2015, at 17:41, Keith Medcalf <kmedcalf at dessus.com> wrote: > > > You also lastly mention that the UUID fields are also used in the selection, > so the problem statement is really: > > Return the projection of Users and Perimeter_Notifications using the common > email field as the equijoin key, but return only the results where there is > not a Devices record with the email and uuid matching the corresponding > fields in Perimeter_Notifications which has Holiday_Mode = 1: > > SELECT * > FROM Users, Perimeter_Notifications > WHERE Users.email = Perimeter_Notifications.email > AND NOT EXISTS (SELECT 1 > FROM Devices > WHERE Devicess.email = Perimeter_Notifications.email > AND Devices.UUID = Perimeter_Notifications.UUID > AND Holiday_Mode = 1); > > Your index on the Devices table will need to include the UUID as in Devices > (email, uuid, holiday_mode ...) (the order within the first three columns of > the index are irrelevant for this query's performance. > >> -----Original Message----- >> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >> bounces at mailinglists.sqlite.org] On Behalf Of Keith Medcalf >> Sent: Saturday, 23 May, 2015 10:26 >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Sample SQL code thats beyond me :( >> >> To re-phrase your problem statement: >> >> Join the table Users and Perimeter_Notifications using the common email >> field and return the results as long as there does not exist a Devices >> record where Holiday_Mode is 1 for that Users email. >> >> Which translates directly to: >> >> SELECT * >> FROM Users, Perimeter_Notifications >> WHERE Users.email = Perimeter_Notifications.email >> AND NOT EXISTS (SELECT 1 >> FROM Devices >> WHERE Devices.email = Users.email >> AND Holiday_Mode = 1); >> >> You should have an index on Devices (email, Holiday_Mode ...), and of >> course you will need an index on Perimeter_Notifications (email ...). You >> could also phrase it as an outer join, but that will be far less efficient >> that the correlated subquery. Some people are in love with outer joins, >> however. You would only need to use an outer join if you also needed some >> data from the Devices table to be returned. >> >> It also has the advantage that when you read it, it translates directly >> back into the original (re-phrased) problem statement, so it is self- >> documenting. >> >> >>> -----Original Message----- >>> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >>> bounces at mailinglists.sqlite.org] On Behalf Of Rob Willett >>> Sent: Saturday, 23 May, 2015 09:41 >>> To: General Discussion of SQLite Database >>> Subject: [sqlite] Sample SQL code thats beyond me :( >>> >>> Hi, >>> >>> I?m trying to do some analysis across a couple of tables and the SQL is >>> beyond my meagre skills. I?m struggling to even describe the problem to >> be >>> honest. >>> >>> The high level description is that I have three tables, Users, Devices >> and >>> Perimeter_Notifications. The high level description is that I want to >>> extract a list of users from a database to send information to if they >> are >>> not on holiday. However I don?t necessarily have the holiday_mode set by >>> the user and so our assumption is that unless the holiday mode is set to >> 1 >>> (they are on holiday) its is assumed to be 0. Its the assumption thats >>> causing the problem. If there is no entries in Perimeter_Notifications >>> thats also fine, no rows get returned. >>> >>> CREATE TABLE "Users" ( >>> "email" TEXT NOT NULL, >>> "password" TEXT NOT NULL, >>> "salt" TEXT NOT NULL, >>> "creation_timestamp" TEXT NOT NULL DEFAULT >>> (datetime('now','localtime')), >>> PRIMARY KEY("email") >>> ); >>> >>> CREATE TABLE "Devices" ( >>> "Email" TEXT NOT NULL, >>> "UUID" text NOT NULL, >>> "Holiday_Mode" integer NOT NULL >>> ); >>> >>> CREATE TABLE "Perimeter_Notifications" ( >>> "Email" text NOT NULL , >>> "UUID" text NOT NULL, >>> "route_id" INTEGER NOT NULL, >>> "day" integer NOT NULL, >>> "hour" integer NOT NULL >>> ); >>> >>> (Please note the UUID is nothing to do with the UUID discussion a few >> days >>> ago, I?m not brave enough to open up that little can of worms, we just >>> happen to have chosen that column name a few months ago for mobile >>> devices. Also in case anybody asks, we're not storing passwords in plain >>> text either). >>> >>> I?m not sure if my database design is wrong or I simply cannot work out >>> the SQL to make it work. It might actually be both :) >>> >>> What I want to do is join the table Users and Perimeter Notifications >>> together but only if the value of Devices.Holiday_Mode is either non >>> existent or if Devices.Holiday_Mode does exist and its 0. If >>> Devices.Holiday_Mode is 1 it means the user is on holiday and don?t send >>> them anything. >>> >>> I can work out the logic if Devices.Holiday_Mode actually exists and is >>> either 1 or 0. Thats pretty basic SQL.However if there is no row in >>> Devices with that Email and UUID then thats the equivalent as >>> Devices.Holiday_Mode being 0. I?ve looked at IS NULL or NOT EXISTS but >> I?m >>> struggling to get my head around it all. I can do all of this in a >> higher >>> level language (not sure if Perl is higher level than SQL) but I should >> be >>> able to do this in SQL itself. >>> >>> Any advice or guidance welcomed please. >>> >>> Thanks for reading, >>> >>> Rob. >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
