Sql – postgreSQL inner join


i have sql query

select * from "Roles"  Join "Users"  On "Roles".Role="Users".RoleId

it return error column Roles.role does not exist


select * from "Roles"  Join "Users"  On Roles.Role=Users.RoleId

return error missing FROM-clause entry for table "roles"

how can i solve this problem?
i aways work with ms sql

Best Solution

You cannot use the name Roles in the join condition. Internally all table names like Roles, ROLES, roles, RoLeS are converted into roles (lower case). When you use "Roles", "ROLES", "roles", "RoLeS" the name is used exactly as you've written that (no lower case convertion) so in the FROM part are taken "Roles" and "Users" tables and in the join condition the table names are roles and users and such tabbles don't exist.

The simples way is to use only table names without "", just use simple Roles instead of "Roles" so you can write Roles or roles regardless the letters lower/upper case.