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



Reply via email to