title: MySQL to select records with one duplicate and one distinct value
body: <p>been struggling with a SELECT SQL query...</p>
  <p>I have a table <code>user</code></p>
  <pre><code>id  |  name    | is_regular |....other columns
  1   |  user1   |      1     |
  2   |  user2   |      1     |
  3   |  user1   |      0     |
  </code></pre>
  <p>I need to create a SELECT query which gives me users with the <strong>same</strong> <code>name</code> but <strong>different</strong> <code>is_regular</code> value.</p>
  <p><code>is_regular</code> is a flag that can have <code>0</code> or <code>1</code> therefore I tried with having clause: <code>HAVING COUNT(DISTINCT is_regular) = 2</code> yet I am unable to fetch duplicate names.</p>
  <p>So, the query would fetch something like...</p>
  <pre><code>id  |  name    | is_regular |
  1   |  user1   |      1     |
  3   |  user1   |      0     |
  </code></pre>
  <p>or even better</p>
  <pre><code>id  |  name  
  1   |  user1
  </code></pre>
  <p>Is there a way to do so without subquery?</p>