title: stored procedure sql server trying to eliminate dups (many to many relationship)
body: <p>A little difficult wording this properly..</p>
  
  <ul>
  <li>Table A has a Role</li>
  <li>Table B ties Roles to Assets</li>
  <li>Table C is for Assets</li>
  </ul>
  
  <p>There is a many to many relationship from Roles to Assets.</p>
  
  <p>Based on a column bit value in Assets.. I want to map that to roles w/ that value.  But I get numerous roles because of the many to many relationship.  So what I want is if ANY asset has bit = 1 return the equivalent role(s).  I do not want duplicate PK for the roles.  My query which returns duplicate roles (by PK) looks like this.</p>
  
  <pre><code>select rls.Role_ID, 
         asset.Asset_ID, 
         Asset.Is_Public
  
          from Roles rls
              inner join Role_Asset on rls.Role_ID = Role_Asset.Role_ID
              inner join Asset on Role_Asset.Asset_ID = asset.Asset_ID 
              where Asset.Is_Public =1
  </code></pre>
  
  <p>problem being I turn numerous rolesID because of the many to many relationship.. If any public asset is tied to a role.. I want that role, but only that role.  If a role is tied to numerous assets and one is 1 and the other is 0. I still want it returned (so if any public asset it tied to a role return it). </p>
  
  <p>AND THE INVERSE (AKA IS_PUBLIC=0)</p>