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
