title: How to find pairs in SQL
body: <p>I have a table containing original records and cancelled records. The original records are marked with code=1 and the cancelled ones are marked with code=2. There are other types of codes as well. I need to find all the original records that do not have a cancellation record in the table.</p>
  
  <p>E.g. Table1</p>
  
  <pre><code>id | column2 | column3 | code
  -- |-------- | ------- | ---
   1 | abc     | def     | 1
   2 | xyz     | pqr     | 1
   3 | abc     | def     | 2
  </code></pre>
  
  <p>The result of the query should be the row with id 2 as it has not been cancelled.</p>
  
  <p>I am trying to use self-join</p>
  
  <pre><code>select * 
  from table1 t1
   join table1 t2 on t1.column1=t2.column1
                 and t1.column2=t2.column2
                 and t1.code&lt;t2.code
  where not (t1.code=1 and t2.code=2)
  </code></pre>