title: The column xxx was specified multiple times for table
body: <p>I am trying to adapt a db2 simple query to SQL SERVER. This query is working fine on db2 </p>
  
  <pre><code>select * 
  from pb_console.users u 
  join ( 
       select * from pb_console.users_user_role j join  
       pb_console.users_roles r on j.role_id = r.role_id) as jj 
  on jj.user_id = u.user_id
  </code></pre>
  
  <p>on sql server it fails with error:</p>
  
  <pre><code>The column 'ROLE_ID' was specified multiple times for 'jj'
  </code></pre>
  
  <p>I have tried removing role _id from the left table of the join as:</p>
  
  <pre><code>select * from pb_console.users u join ( 
      select user_id, role_rif from 
      pb_console.users_user_role j join (select role_id, role_name from 
  pb_console.users_roles) r 
  on 
  j.role_id = r.role_id) as jj on jj.user_id = u.user_id
  </code></pre>
  
  <p>but resulting in.</p>
  
  <pre><code>The column 'role_id' was specified multiple times for 'jj'.
  </code></pre>
  
  <p>I have also tried using a different alias for the first role_id, with no success.</p>
  
  <p>How can I fix this?</p>