CODEWITHSUNDEEP
sqlpostgresqlinner-join
kusanagi
kusanagi

Reputation: 14624

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

query 

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

Upvotes: 0

Views: 6395

Answers (3)

araqnid
araqnid

Reputation: 133752

I suspect you needed to write "Roles"."Role" = "Users"."RoleId", because it's complaining about not being able to find the lower-case column name.

As others have mentioned, it's usually easiest to create everything as lower case, even if you use mixed case in queries: so if the table is called "roles" and the column "role" etc. you can write Roles.Role = Users.RoleId and the identifiers will be converted to lower case behind the scenes.

Upvotes: 0

Szymon Lipiński
Szymon Lipiński

Reputation: 28664

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.

Upvotes: 2

Wael Dalloul
Wael Dalloul

Reputation: 23044

write it like this way:

select * from Roles  INNER JOIN Users  On (Roles.Role= Users.RoleId)

check INNER JOIN two tables for more info...

or you can use the simple way that it works with most DBMS:

select * from Roles,Users where Roles.Role= Users.RoleId

Upvotes: 1

Related Questions