I have a h2 database setup. In my tables, that are being used by the sql query, there are no ambiguous column names, but the query always fails due to that.

db setup

I've tried to minimise the sql statement and removing the inner joins seem to remove the problem, though it also removed the functionality.

I've also tried to specify a table name for every column in the query, with no luck.

Select user.username, roles.role
from User
inner join user-roles on(user.id=user-roles.user_id)
inner join roles on(user-roles.roles_id=roles.id)
where user.username='root';

1 Answers

0
mkRabbani On Best Solutions

Can you please check this...

SELECT 
U.username, 
R.role
FROM [User] U
INNER JOIN [user-roles] UR ON U.id = UR.[user_id]
INNER JOIN [roles] R ON UR.role_id=R.id
WHERE U.username='root';