Column in Query Won't Match

Transact-SQL
,
1

Hi I'm using SQL Server Management Studio - in my query below I'm trying to select the Name.ID column but when I run the query I get the error:

ERROR Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'id'.

QUERY RUNNING:

select n.id, email,member_type,first_name, last_name,left(first_name,1)+last_name,u.*
-- update u set u.web_login=n.email, u.web_password=left(first_name,1)+last_name --
from name n
join name_security u on id=u.id
where n.email<>'' and web_login=''

Any help would be great.

2

At minimum:
join name_security **n.**u on id=u.id

Better, when there's more than one table in a query, use the table alias on every column reference.

select n.id, ?.email,member_type,?.first_name, ?.last_name,left(?.first_name,1)+?.last_name,u.*
from name n
join name_security u on n.id=u.id
where n.email<>'' and ?.web_login=''

1 Like
3

Hey SP,

Thanks for the reply - I should have added the "n" to ID on the join name_security u on line.

select n.id, email,member_type,first_name, last_name,left(first_name,1)+last_name,u.*
-- update u set u.web_login=n.email, u.web_password=left(first_name,1)+last_name
from name n
join name_security u on n.id=u.id
where n.email<>'' and web_login=''

DJ