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'
WHERE UserName LIKE '%Hwl123%';
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?