title: DISTINCT or GROUP BY used with NOT IN returns wrong results
body: <p>I have two tables<br>
  users with columns: uid, name, mail, etc<br>
  users_roles with columns: uid, rid  </p>
  
  <p>In users_roles, each time a role is added to a user, it is listed in the users_roles table. So, let's say user 1 has roles 1 and 4. In the table:  </p>
  
  <pre><code>users_roles:  
  uid | rid  
   1  |  1  
   1  |  4  
  </code></pre>
  
  <p>I need to return all users who don't have roles 4 OR 5. I have tried using both Group By and Distinct combined with NOT IN. The problem I keep running into is if a user has both roles 1 and 4, they will be returned in the results. Below is a an example of my Group By query:</p>
  
  <pre><code>SELECT *
  FROM users AS u
  LEFT JOIN users_roles AS ur ON u.uid = ur.uid
  WHERE ur.rid NOT
  IN ( 4, 5 )
  GROUP BY ur.uid
  </code></pre>
  
  <p>I have tried sub-queries as well to no avail because the issue seems to be that Group By combines rows after finishing the query. So, it simply finds the record containing uid 1 rid 4 and returns it in the results.  </p>
  
  <p>The Drupal module Views that I can't use (due to security issues with Views Bulk Operations) accomplishes the desired results by doing the following:  </p>
  
  <pre><code>LEFT JOIN users_roles ON users.uid = users_roles.uid 
  AND (users_roles.rid = '4' OR users_roles.rid = '5')
  </code></pre>
  
  <p>For long term maintenance I don't want to have to update the code every single time we add a role and this is going to make for one long query.  </p>
  
  <p>I looked at the following:<br>
  <a href="https://stackoverflow.com/a/4768499">Aggregating Rows</a><br>
  <a href="https://stackoverflow.com/questions/10796468/filtering-distinct-rows-in-sql">Filtering distinct rows in SQL</a>  </p>
  
  <p>While there are Drupal functions that will let me get the list of role ids where I could unset the roles I don't want show up in the resulting array, I feel like I am missing a fundamental understanding of SQL. Is there a better way to do this in SQL?</p>