Re: [sqlite] Is this really the best way to do this?

There is no difference. The problem statement was: Look up the mail address (which is constrained unique) and return the defined action (which is constrained NOT NULL). If there is no "action" (which can be only because the mail does not exist) return OK. Unless, of course, the initial

Re: [sqlite] Is this really the best way to do this?

Hello, On 2018-05-17 10:40, Dominique Devienne wrote: On Wed, May 16, 2018 at 8:33 PM Keith Medcalf wrote: SELECT coalsce((select action from blocked where mail='...'), 'OK') as action; Nice one Keith. Works (see below), but I

Re: [sqlite] Is this really the best way to do this?

On Wed, May 16, 2018 at 8:33 PM Keith Medcalf wrote: > > SELECT coalsce((select action > from blocked > where mail='...'), 'OK') as action; > Nice one Keith. Works (see below), but I find it a bit intuitive, since returning no row is

Re: [sqlite] Is this really the best way to do this?

SELECT coalsce((select action from blocked where mail='...'), 'OK') as action; --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users

Re: [sqlite] Is this really the best way to do this?

On 2018/05/16 6:21 PM, Dominique Devienne wrote: On Wed, May 16, 2018 at 6:12 PM Clemens Ladisch wrote: Stephen Chrzanowski wrote: On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch wrote: SELECT action FROM blocked WHERE email = ? UNION ALL SELECT

Re: [sqlite] Is this really the best way to do this?

On Wed, May 16, 2018 at 6:12 PM Clemens Ladisch wrote: > Stephen Chrzanowski wrote: > > On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch > wrote: > >> SELECT action FROM blocked WHERE email = ? > >> UNION ALL > >> SELECT 'OK' > >> LIMIT 1; > > > > Out of

Re: [sqlite] Is this really the best way to do this?

Stephen Chrzanowski wrote: > On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch wrote: >> SELECT action FROM blocked WHERE email = ? >> UNION ALL >> SELECT 'OK' >> LIMIT 1; > > Out of curiosity, where's the guarantee that the OK won't be displayed if > an email is found? The

Re: [sqlite] Is this really the best way to do this?

Clemens, Paul S, Abroży, Thank you for taking the time to reply. I'll look at each of them and see what makes the most sense to me. It's always good to ask the people here as there is a wealth of experience. As suggestions popped in, I kept thinking go the unix competition which was to

Re: [sqlite] Is this really the best way to do this?

Out of curiosity, where's the guarantee that the OK won't be displayed if an email is found? Do all unions show up in the return result based on where in the query they're called? On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch wrote: > > SELECT action FROM blocked WHERE

Re: [sqlite] Is this really the best way to do this?

Rob Willett wrote: > select CASE > WHEN EXISTS (select 1 from blocked where email = > 'rwillett.dr...@example.com') > THEN (select action from blocked where email = > 'rwillett.dr...@example.com') > ELSE 'OK' > END SELECT action FROM blocked WHERE email = ? UNION ALL SELECT 'OK'

Re: [sqlite] Is this really the best way to do this?

or more in tune with the default value in the schema SELECT CASE count(*) WHEN 0 THEN 'Ok' ELSE action END FROM blocked WHERE email = 'rwillett.dr...@example.com'; Paul www.sandersonforensics.com SQLite Forensics Book On 16 May 2018 at 09:22, Rob

Re: [sqlite] Is this really the best way to do this?

How about something like SELECT CASE count(*) WHEN 0 THEN 'Discard' ELSE 'Ok' END FROM blocked WHERE email = 'rwillett.dr...@example.com'; Paul www.sandersonforensics.com SQLite Forensics Book On 16 May 2018 at 10:35, Abroży Nieprzełoży <

Re: [sqlite] Is this really the best way to do this?

I would make something like this: CREATE TABLE blocked( mail TEXT PRIMARY KEY, action TEXT NOT NULL DEFAULT 'DISCARD' ) WITHOUT ROWID; INSERT INTO blocked(mail) VALUES('badm...@example.com'); SELECT coalesce(action, 'OK') AS action FROM (SELECT 'goodm...@example.com' AS mail)