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