title: SQL Server Distinct Union for one column
body: <p>I'm having a rough time figuring out the SQL query I need for a situation.</p>
  
  <p>I have a project that has studio level user roles for a user, and each project has project level roles that overlay/override the studio level roles. All roles are defined at the studio level, but only some roles are defined at the project level (mainly roles that have different values than their corresponding studio level role)</p>
  
  <p><strong>g_studio_UsersInRole</strong></p>
  
  <pre><code>userId  roleId  value
  1       1       TRUE
  1       2       TRUE
  1       3       TRUE
  2       1       FALSE
  </code></pre>
  
  <p><strong>g_project_UsersInRole</strong></p>
  
  <pre><code>userId  roleId  value    projectId
  1       2       FALSE    1
  2       1       TRUE     1
  </code></pre>
  
  <p>I need a query that overlays the project roles over the studio roles for a given project Id. The tricky part is avoiding the duplicate studio level role. I need the project level roles (if any) to dominate.</p>
  
  <p>I've been playing with Unions, but I can't figure out how to avoid the duplicates.</p>
  
  <p>Basically I need the following results:</p>
  
  <pre><code>userId  roleId  value
  1       1       TRUE
  1       2       FALSE
  1       3       TRUE
  2       1       TRUE
  </code></pre>
  
  <p>Where </p>
  
  <ul>
  <li>userId of 1, roleId of 2 has a value False</li>
  <li>userId of 2, roleId of 1 has a value True</li>
  </ul>
  
  <p>as indicated in the project level</p>
  
  <p>I thought I was close with this query, but the duplicates are still present:</p>
  
  <pre><code>;With roles As
  (
  SELECT     UserId, Value, RoleId
                         FROM          dbo.g_project_UsersInRole
                         WHERE      (ProjectId = 1)
                         UNION
                         SELECT     UserId, Value, RoleId
                         FROM         dbo.g_studio_UsersInRole)
  
  SELECT     roles.RoleId, Value, UserId
  FROM        roles
  RIGHT JOIN (SELECT DISTINCT RoleId FROM roles) AS distinctRoles
  ON distinctRoles.RoleId = roles.RoleId
  </code></pre>