CODEWITHSUNDEEP
c#asp.netsql
Color Shadow
Color Shadow

Reputation: 305

Error while join table in SQL

I have two tables that have relationship with UserName field. Based on UserName column, I want to get ID from CarReserve table. Here is my SQL statement:

SELECT CarReserve.ID 
FROM CarReserve 
 INNER JOIN aspnet_Users ON CarReserve.UserName = aspnet_Users.UserName 
WHERE UserName = @UserName

Unfortunately, I am getting this warning:

Ambiguous column name "UserName"

Can anyone here tell me what is wrong with my statement?

Upvotes: 0

Views: 59

Answers (3)

Himanshu
Himanshu

Reputation: 32602

Add table name before UserName in WHERE UserName = @UserName

Your query should be:

SELECT CarReserve.ID 
    FROM CarReserve 
    INNER JOIN aspnet_Users ON CarReserve.UserName = aspnet_Users.UserName 
    WHERE aspnet_Users.UserName = @UserName
    ------^-----------^----------------

Or

SELECT CarReserve.ID 
    FROM CarReserve 
    INNER JOIN aspnet_Users ON CarReserve.UserName = aspnet_Users.UserName 
    WHERE CarReserve.UserName = @UserName
    ------^---------^----------------

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

You have to alias the username field in the WHERE clause to be unambiguously qualified:

SELECT CarReserve.ID 
FROM CarReserve 
INNER JOIN aspnet_Users ON CarReserve.UserName = aspnet_Users.UserName 
WHERE aspnet_Users.UserName = @UserName

Upvotes: 1

Najzero
Najzero

Reputation: 3202

You forgot to add aspnet_users. before the username in your where clause.

I may suggest using aliases for your tables too.

Upvotes: 1

Related Questions