sqlite select where...and vs. case...when

Question

I'm new to sqlite and have a populated database where I need to determine if any of the digital organisms in the database have a threshold level of fitness (fitness>=2) and are alive (live=1). I also need to exclude the indivID (the Primary Key) of the organism for whom I am trying to find a fit mate (indivID INTEGER PRIMARY KEY).

Below I tried the first line of code but it threw a SQL error:

[SQLITE_ERROR] SQL error or missing database (near "CASE": syntax error).

However I know the error is in the SQL statment because other functions are successfully accessing the database.

SELECT indivID FROM population CASE fitness >=2 WHEN live = 1 AND indivID!=[specific individual] ELSE NULL END

I have tried this next line of code, which works but does not properly exclude the indivID of the specific individual:

SELECT [some column names] FROM [a database] WHERE fitness>=2 AND live=1 AND indivID!=[specific individual]

I have three questions:

1) Where are my errors in the above statements

2) What is the difference between using "case...when" and "where...and" statements

3) It is possible and even probable on these queries that there may not be a "live=1" individual with high enough fitness (above 2) to qualify for the select statement, so will the outcome of both of these queries with no viable individual (if correctly written) then be null?

Thanks in advance for your help!

Answer 1

According to your first query, you seem to misunderstand the usage of case when. It's like an if for a single value. It's usually used to get the required value based on some column values, e.g.

SELECT CASE WHEN col>0 THEN 1 ELSE 0 END FROM table

However it can also be used for some nifty tricks on the condition, e.g.

SELECT col FROM table WHERE CASE WHEN some_param = 1 THEN col2 = 1 ELSE true END

This statement retrieves column col for rows where col2 = 1 if some input parameter is 1 or for all rows if input parameter is something else than 1.

Using WHERE ... AND checks the condition, e.g.

SELECT col FROM table WHERE col>0 AND col2=0

In your case, you're trying to select a null if no matching values are found. I don't think it's the right way of dealing with it. You should attempt to select what you're looking for and then check whether you got any rows back. You would essentially get to something like this:

ResultSet rs = stmt.executeQuery("SELECT [some column names] FROM [a database] WHERE fitness>=2 AND live=1 AND indivID!=[specific individual]");
if(rs.first()) {
    //You have some data - loop over the resultset and retrieve it
}
else {
    //There are no matches to your query
}

If you only want one match, then you can simplify this to

ResultSet rs = stmt.executeQuery("SELECT [some column names] FROM [a database] WHERE fitness>=2 AND live=1 AND indivID!=[specific individual] LIMIT 1");
String name = rs.first() ? rs.getString(1) : null;

Note that I used index 1 for getString - but it may be whatever you need.

EDIT: If you're dealing with SQLite, then you have a single-direction-moveable resultset, therefore you need to change the above code slightly:

ResultSet rs = stmt.executeQuery("SELECT [some column names] FROM [a database] WHERE fitness>=2 AND live=1 AND indivID!=[specific individual]");
if(!rs.isBeforeFirst()) {
    //You have some data - loop over the resultset and retrieve it
}
else {
    //There are no matches to your query
}

And, correspondingly,

ResultSet rs = stmt.executeQuery("SELECT [some column names] FROM [a database] WHERE fitness>=2 AND live=1 AND indivID!=[specific individual] LIMIT 1");
String name = !rs.isBeforeFirst() ? null : rs.getString(1) : null;;

Note the change of rs.first() to !rs.isBeforeFirst() and the reversal of the condition.