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
