title: mysql - select distinct mutually exclusive (based on another column's value) rows
body: <p>First off, I would like to say that if after reading the question, anyone has a suggestion on a more informative title for this question, please tell me, as I think mine is somewhat lacking, now, on to business...</p>
  
  <p>Given this table structure:</p>
  
  <pre><code>+---------+-------------------------------------+------+-----+---------+----------------+
  | Field   | Type                                | Null | Key | Default | Extra          |
  +---------+-------------------------------------+------+-----+---------+----------------+
  | id      | int(11)                             | NO   | PRI | NULL    | auto_increment |
  | account | varchar(20)                         | YES  | UNI | NULL    |                |
  | domain  | varchar(100)                        | YES  |     | NULL    |                |
  | status  | enum('FAILED','PENDING','COMPLETE') | YES  |     | NULL    |                |
  +---------+-------------------------------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>And this data:</p>
  
  <pre><code>+----+---------+------------------+----------+
  | id | account | domain           | status   |
  +----+---------+------------------+----------+
  |  1 | jim     | somedomain.com   | COMPLETE |
  |  2 | bob     | somedomain.com   | COMPLETE |
  |  3 | joe     | somedomain.com   | COMPLETE |
  |  4 | frank   | otherdomain.com  | COMPLETE |
  |  5 | betty   | otherdomain.com  | PENDING  |
  |  6 | shirley | otherdomain.com  | FAILED   |
  |  7 | tom     | thirddomain.com  | FAILED   |
  |  8 | lou     | fourthdomain.com | COMPLETE |
  +----+---------+------------------+----------+
  </code></pre>
  
  <p>I would like to select all domains which have a 'COMPLETE' status for all accounts (rows).</p>
  
  <p>Any domains which have a row containing any value other then 'COMPLETE' for the status must not be returned.</p>
  
  <p>So in the above example, My expected result would be: </p>
  
  <pre><code>+------------------+
  | domain           |
  +------------------+
  | somedomain.com   |
  | fourthdomain.com |
  +------------------+
  </code></pre>
  
  <p>Obviously, I can achieve this by using a sub-query such as: </p>
  
  <pre><code>mysql&gt; select distinct domain from test_table where status = 'complete' and domain not in (select distinct domain from test_table where status != 'complete'); 
  +------------------+
  | domain           |
  +------------------+
  | somedomain.com   |
  | fourthdomain.com |
  +------------------+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>This will work fine on our little mock-up test table, but in the real situation, the tables in question will be tens (or even hundreds) of thousands of rows, and I'm curious if there is some <em>more efficient</em> way to do this, as the sub-query is slow and intensive.</p>