CODEWITHSUNDEEP
sqlsql-serverssms
Efrin
Efrin

Reputation: 2423

MS SQL - performing a simple join

I am learning MS SQL from scratch and I am trying to create a join on my table but there's something wrong with my query:

SELECT * FROM Users.[User] JOIN Users.[User] as u ON (u.id = Users.[UserAddress].user);


Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user'.

table structure

What's wrong :)?

Upvotes: 0

Views: 84

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

user is a reserved word in SQL Server, so you need to enclose it in square braces. The list of reserved words is here.

I would recommend writing your query as:

SELECT *
FROM Users.[User] u JOIN
     Users.UserAddress ua
     ON u.id = ua.[user];

Note: this also fixes the problem that you seem to want to join to UserAddress and not a self-join to User.

And some advice:

  • Use table aliases for all your tables. They make queries easier to read and write.
  • The table aliases should be abbreviations for the tables.
  • Avoid using reserved words for table and column names.
  • Avoid square braces (unless you really need them); they make queries harder to write and to read.

In addition, I usually give my tables names in the plural (Users) with a primary key in the singular (UserId), and then always try to have foreign keys have the same name (UserId in both cases).

Upvotes: 6

Alexander
Alexander

Reputation: 20224

User is a keyword, so to access a column "user", you have to put it into square brackets:

SELECT * FROM Users.[User] JOIN Users.[User] as u ON (u.id = Users.[UserAddress].[user]);

Upvotes: 0

Related Questions