title: sqlite select where...and vs. case...when
body: <p>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 (<strong>indivID INTEGER PRIMARY KEY</strong>). </p>
  
  <p>Below I tried the first line of code but it threw a SQL error:</p>
  
  <blockquote>
    <p>[SQLITE_ERROR] SQL error or missing database (near "CASE": syntax
    error).</p>
  </blockquote>
  
  <p>However I know the error is in the SQL statment because other functions are successfully accessing the database.</p>
  
  <pre><code>SELECT indivID FROM population CASE fitness &gt;=2 WHEN live = 1 AND indivID!=[specific individual] ELSE NULL END
  </code></pre>
  
  <p>I have tried this next line of code, which works but does not properly exclude the indivID of the specific individual:</p>
  
  <pre><code>SELECT [some column names] FROM [a database] WHERE fitness&gt;=2 AND live=1 AND indivID!=[specific individual]
  </code></pre>
  
  <p>I have three questions:</p>
  
  <p>1) Where are my errors in the above statements</li></p>
  
  <p>2) What is the difference between using "case...when" and "where...and" statements</p>
  
  <p>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?</p>
  
  <p>Thanks in advance for your help!</p>