title: Select value that does not exist in another column of the same table, MySQL Database Not Exists Clause
body: <p>What I'm trying to do here is to select all <em>contract_id</em> that does not exist in the <em>prev_contract_id</em> column and whereby the end date is earlier than a date.</p>
  
  <p>Here is my database table called <strong>contract</strong>, and the values are in
  (<em>contract id</em>, <em>contract type</em>, <em>start date</em>, <em>end date</em>, <em>prev contract id</em>, <em>staff id</em>) columns</p>
  
  <pre><code>insert into contract values('co-0000001', 'Monthly', '2014-01-01', '2014-02-01','null', 'staff3');
  insert into contract values('co-0000002', 'Yearly', '2013-07-07', '2014-07-06','null', 'staff3');
  insert into contract values('co-0000003', 'Monthly', '2014-03-20', '2014-04-19','co-0000001', 'staff4');
  insert into contract values('co-0000004', 'Yearly', '2014-02-27', '2015-03-27','null', 'staff4');
  insert into contract values('co-0000005', 'Ad-hoc', '2014-02-27', '2015-03-27','null', 'staff4');
  insert into contract values('co-0000006', 'Yearly', '2013-02-27', '2014-02-27','null', 'staff4');
  </code></pre>
  
  <p><strong>Here is my SQL statement</strong></p>
  
  <pre><code>select contract_id from contract 
  where not exists(select prev_contract_id from contract where prev_contract_id &lt;&gt; 'null') 
  and end_date &lt; '2014-05-05';
  </code></pre>
  
  <p><strong>The results of each individual select statements</strong></p>
  
  <pre><code>select prev_contract_id from contract where prev_contract_id &lt;&gt; 'null';
  </code></pre>
  
  <p>returns me <code>co-0000001</code> contract_id, and</p>
  
  <pre><code>select contract_id from contract where end_date &lt; '2014-05-05';
  </code></pre>
  
  <p>returns me <code>co-0000001</code>, <code>co-0000003</code>, <code>co-0000006</code>.
  I want to eliminate <code>co-0000001</code> from the selection and only return me co-0000003 and co-0000006.</p>
  
  <p>It works when i run them separately, but when i combine them together using the <code>NOT EXISTS</code> clause, I was not able to get my results. I do not want to use <code>IN</code> as I heard it's more time consuming or inefficient.</p>