sql server - SQL SELECT Query: "Ambiguous column name"

Question

I have never written anything but basic SQL queries. I am trying to join two tables and I am getting this error :

Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'UserId'.

This is my SQL query :

SELECT UserName, UserId
FROM aspnet_Users 
JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId

And this is my database diagram:

What is wrong with my query?

Also could anyone tell how should I structure this query in order to get the UserName and RoleName?

Answer 1

You must specify the table name when using multiple tables since UserId appears in both tables.

Assuming you want UserName and UserId from aspnet_users:

SELECT aspnet_Users.UserName, aspnet_Users.UserId
FROM aspnet_Users JOIN aspnet_UsersInRoles 
ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId