title: SQL filter out ids that have multiple values
body: <p>I have MySQL table in the following format </p>
  
  <pre><code>id     c1     c2
  ---------------------
  id1    c1v1   c2v1  -&gt; keep id1 even it has duplicates
  id1    c1v1   c2v1
  id2    c1v2   c2v2  -&gt; filter out id2 because it has various c1, c2
  id2    c1v2   c2v3
  id3    c1v3   c2v4
  ....
  </code></pre>
  
  <p>Expected output: </p>
  
  <pre><code>id     c1     c2
  ---------------------
  id1    c1v1   c2v1
  id3    c1v3   c2v4
  ....
  </code></pre>
  
  <p>I want to retain only the records where id only has a unique c1, c2 value.
  I do have a solution but it requires full table scan twice including a join which is very inefficient and I am wondering if there is a better way of doing it.</p>
  
  <pre><code>select 
      distinct id, c1, c2
  from table 
  inner join 
      (select 
          id, 
          count(distinct c1, c2) as counts
      from table 
      group by id
      having counts = 1) tmp
  on table.id = tmp.id
  </code></pre>