CODEWITHSUNDEEP
databasesqlite
Evan Cortens
Evan Cortens

Reputation: 790

"if, then, else" in SQLite

Without using custom functions, is it possible in SQLite to do the following. I have two tables, which are linked via common id numbers. In the second table, there are two variables. What I would like to do is be able to return a list of results, consisting of: the row id, and NULL if all instances of those two variables (and there may be more than two) are NULL, 1 if they are all 0 and 2 if one or more is 1.

What I have right now is as follows:


SELECT 
    a.aid, 
    (SELECT count(*) from W3S19 b WHERE a.aid=b.aid) as num, 
    (SELECT count(*) FROM W3S19 c WHERE a.aid=c.aid AND H110 IS NULL AND H112 IS NULL) as num_null, 
    (SELECT count(*) FROM W3S19 d WHERE a.aid=d.aid AND (H110=1 or H112=1)) AS num_yes 
FROM W3 a

So what this requires is to step through each result as follows (rough Python pseudocode):


if row['num_yes'] > 0:
    out[aid] = 2
elif row['num_null'] == row['num']:
    out[aid] = 'NULL'
else:
    out[aid] = 1

Is there an easier way? Thanks!

Upvotes: 6

Views: 20835

Answers (2)

Berry Tsakala
Berry Tsakala

Reputation: 16610

There's another way, for numeric values, which might be easier for certain specific cases. It's based on the fact that boolean values is 1 or 0, "if condition" gives a boolean result:

(this will work only for "or" condition, depends on the usage)

SELECT (w1=TRUE)*r1 + (w2=TRUE)*r2 + ...

of course @evan's answer is the general-purpose, correct answer

Upvotes: 1

jmz
jmz

Reputation: 5479

Use CASE...WHEN, e.g.

CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END

Read more from SQLite syntax manual (go to section "The CASE expression").

Upvotes: 23

Related Questions