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.
