Create SQL View in another database from 2 tables in existing database. Mdsh 1013

I have two databases A & B. I need to create a view in database A from 2 tables in database B. My objective is to have Name, MiddleName, Last Name, and badge number in the view which will be located in database A.
Use A
create view EmployeeWork
      Select B.dbo.inf_employee.Name,
      from B.dbo.inf_employee
      INNER JOIN B.dbo.inf_employee on B.dbo.inf_employee.badge = B.dbo.inf_badge_repo.badge

When I run this code, I get the following error messages " Msg 1013, Level 16, State 1, Procedure EmployeeWork, Line 3
The objects "B.dbo.inf_employee" and "B.dbo.inf_employee" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you need to use aliases  see this article
a different alias for the same table, and it will work
rodneygrayAuthor Commented:
Great article
Vitor MontalvãoMSSQL Senior EngineerCommented:
Rodney, the article is enough to help you or you need more input from the Experts?
rodneygrayAuthor Commented:
My issue turned out to be the method used to join the tables. I had to use the CAST function to convert the linking fields to an integer value. Final code that worked:
use databasename
create view EmployeeWork
      Select A.table1.Name As FirstName
               ,A.table1.Image as photo
               , as DeptName
      from A.table1
      inner JOIN A.table3 on cast(A.table3.badge as int) = cast(A.table1.badge as int)
      inner join A.table2 on = A.table1.groupid

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.