Getting unique column names to allow a creation of a view?

Question

I'm currently trying to create a view from two tables by doing a join, I've methods used here but i'm still getting this error:

Msg 4506, Level 16, State >1, Procedure vw_Employee, Line 3 [Batch Start Line 19]
Column names in each view or function must be unique. Column name 'empNumber' in view or function 'vw_Employee' is specified more than once.

My view is as follows:

create view vw_Employee as
(
    select * 
    from Employees e1 
    inner join EmpInfo e2 on e2.empNumber = e1.empNumber
)

Answer 1

It's because both the tables have empNumber column and you are using * to select. There shouldnt be any ambiguity/collisions in column names in the select. Assuming the only common column name is empNumber.

create view vw_Employee as
(
select * from Employees e1 inner join EmpInfo e2 
using (empNumber)
)

I'd recommend you to explicitly list all the column names you need.

create view vw_Employee as
(
select e1.empNumber, . . .
from Employees e1 inner join EmpInfo e2 
on e2.empNumber = e1.empNumber
)

EDIT:

Based on your comment, try:

create view vw_Employee
as
(
        select e1.empNumber,
            e1.firstName,
            e1.lastName,
            e1.ssn,
            e1.job,
            e1.projectId,
            e2.[address],
            e2.[state],
            e2.zip,
        from Employees e1
        inner join EmpInfo e2 on e2.empNumber = e1.empNumber
        )