title: How to "remove duplicates" from a UNION query
body: <p>I have two tables in MySQL: One called <code>gtfsws_users</code> which contains users for a system I'm developing and another called <code>gtfsws_repository_users</code> which contains roles for these users.</p>
  
  <p><code>gtfsws_users</code> has these fields: <strong>email</strong>, <strong>password</strong>, <strong>name</strong>, <strong>is_admin</strong> and <strong>enabled</strong>.
  <code>gtfsws_repository_users</code> has: <strong>user_email</strong>, <strong>repository_id</strong> and <strong>role</strong>.</p>
  
  <p>The <code>role</code> is an integer that defines privileges over a GTFS repository (public transportation data, not relevant for my problem).</p>
  
  <p>One important thing is that <strong>every administrator accont</strong> (that is, every user that has the <code>is_admin</code> flag set as <code>1</code> in <code>gtfsws_users</code>) has full access to all repositories.</p>
  
  <p>Now, only users registered in <code>gtfsws_repository_users</code> will have access to a specific repository defined there (unless they are administrators, of course). One user can have multiple repositories which him/her can access.</p>
  
  <p>What I'm trying to do is to get <strong>all users with access to a specific repository</strong> (it doesn't matter which type of <code>role</code> the user has, I just want to know if they can access the repository or not). So I'm writing this SQL statement for getting them:</p>
  
  <pre><code>(
      SELECT DISTINCT
          gtfsws_users.email AS email,
          gtfsws_users.name AS name,
          gtfsws_users.is_admin AS is_admin,
          gtfsws_users.enabled AS enabled,
          gtfsws_repository_users.role AS role
      FROM
          gtfsws_users
      INNER JOIN
          gtfsws_repository_users
      ON
          gtfsws_users.email = gtfsws_repository_users.user_email
      WHERE
          gtfsws_repository_users.repository_id = '2'
  )
  UNION
  (
      SELECT
          email,
          name,
          is_admin,
          enabled,
          null AS role
      FROM
          gtfsws_users
      WHERE
          is_admin = 1
  )
  </code></pre>
  
  <p>Now, this works fine for users with access to different repositories. It also gets all administrators too.</p>
  
  <p>The problem is when I have an administrator that is also registered in <code>gtfsws_repository_users</code>, because I get it duplicated.</p>
  
  <p>So for example, it I have this in <code>gtfsws_users</code>:</p>
  
  <p><code>('test@test.com', '*****', 'Real name', 1, 1)</code></p>
  
  <p>And also the user is registered in <code>gtfsws_repository_users</code> as this:</p>
  
  <p><code>('test@test.com', 2, 10)</code></p>
  
  <p>When I do the <code>SELECT</code> in MySQL (using the <code>UNION</code> to add all administrators) I get:</p>
  
  <pre><code>('test@test.com', 'Real name', 1, 1, 10)
  ('test@test.com', 'Real name', 1, 1, NULL)
  </code></pre>
  
  <p>What I need to do is to <strong>filter</strong> that table to remove duplicates, that is getting only:</p>
  
  <pre><code>('test@test.com', 'Real name', 1, 1, NULL)
  </code></pre>
  
  <p>Yes, getting <code>NULL</code> as the role (since it will be ignored as the user is an administrator).</p>
  
  <p>Does anybody have a clue on how to achieve that?
  Thanks a lot.</p>
  
  <p><strong>EDIT</strong>: Ok, thanks to Katrin's suggestion, I'm getting some progress. I do get one row, but it's the one with the <code>role</code> number defined. Any way to preserve the one with the <code>NULL</code> role instead of the defined one?</p>