Stack Holder Stack Holder - 5 months ago 14
SQL Question

Ambiguous Column Name When Selecting From Multiple Tables

I am using SQL Server 2012 and during the selection of UserName from different tables I get the following error:


Msg 209, Level 16, State 1, Line 6

Ambiguous column name 'UserName'


Code:

SELECT *
FROM Signup,
Pers_D,
Edu_D,
Dep_S
WHERE UserName LIKE '%Hwl123%';

Answer

This cannot be accomplished in that way. Comma says - you are trying to join those tables. Not union.

You are getting error Ambiguous column name 'UserName' because several tables have column with that name and server does not understand where to apply this filter.

In simple statements solution could look like:

SELECT *
FROM   Signup as s
WHERE  s.UserName LIKE '%Hwl123%'

UNION ALL

SELECT *
FROM   Pers_D as p
WHERE  p.UserName LIKE '%Hwl123%'

UNION ALL

SELECT *
FROM   Edu_D as e
WHERE  e.UserName LIKE '%Hwl123%'

UNION ALL

SELECT *
FROM   Dep_S as d
WHERE  d.UserName LIKE '%Hwl123%'

This is literally what you are attempting to do.

But your approach is leading you somewhere there:

SELECT s.*, p.*, e.*, d.*
FROM   Signup s
INNER JOIN Pers_D p ON p.??? = s.???
INNER JOIN Edu_D e on e.??? = ???
INNER JOIN Dep_S d on d.??? = ???
WHERE  [???].UserName LIKE '%Hwl123%' ---<<< ambiguity: s.UserName? p.UserName? e.UserName? d.UserName?
Comments