The column xxx was specified multiple times for table

Question

I am trying to adapt a db2 simple query to SQL SERVER. This query is working fine on db2

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

on sql server it fails with error:

The column 'ROLE_ID' was specified multiple times for 'jj'

I have tried removing role _id from the left table of the join as:

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

but resulting in.

The column 'role_id' was specified multiple times for 'jj'.

I have also tried using a different alias for the first role_id, with no success.

How can I fix this?

Solution 1

The error is raised because the ColumnNameROLE_ID is present in both the table pb_console.users_user_role & pb_console.users_roles so try to specify the columns that are only required as below

SELECT * 
FROM pb_console.users u 
join ( 
     SELECT J.RoleID,J.User_ID 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

Solution 2

select * from pb_console.users

Syntax should not be used.

The static way to handle this : In SSMS editor, right clic on the pb_console.users table to get the "select *" detailled syntax... And override alias names as previously told by Joby or Sandip.

The dynamic way : Create a stored procedure to retrieve the current structure of the table to build an sql statement run through "EXECUTE"... Harder way...

Solution 3

You got error : Incorrect syntax near the keyword 'from' in your code because you have not specified any column in select statement, please specify required column in your below query statement

select from pb_console.users_roles

It should be like

select role_id from pb_console.users_roles

For Error:

The column 'role_id' was specified multiple times for 'jj'.

try with alias.columnname in all select statement

select * from pb_console.users u join ( 
    select j.user_id, j.role_id 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