title: Merging Database Tables Using Views and Unions and Innerjoin Views
body: <p>I have a situation where I want have a global view of all the data in multiple databases.  Each database contains the same tables.  </p>
  
  <pre><code>Database: DB1
  Table: Companies
  CompanyID      CompanyName
  ---------------------------------
  1              Disney
  2              Marvel
  3              DC
  
  Table: Employees
  Employee ID    CompanyID      CompanyName      EmployeeName
  ------------------------------------------------------------
  1              1              Disney           Donald Duck
  2              1              Disney           Mickey Mouse
  3              2              Marvel           Spiderman
  4              2              Marvel           Captain America
  5              3              DC               Superman
  6              3              DC               Batman
  
  Database: DB2
  Table: Companies
  CompanyID      CompanyName
  ---------------------------------
  1              Warner Brothers
  2              Hanna Barbera
  
  Table: Employees
  Employee ID    CompanyID      CompanyName      EmployeeName
  ------------------------------------------------------------
  1              1              Warner Brothers  Wolverine
  2              1              Warner Brothers  Cyclops
  3              2              Hanna Barbera    Fred Flinstone
  4              2              Hanna Barbera    Barney Rubble
  </code></pre>
  
  <p>I created the following Views in Database, DB1:</p>
  
  <pre><code>CREATE VIEW [CompaniesView]
  as
  select *
  from [Companies] A
  union all
  select *
  from DB2.dbo.[Companies] B
  
  CREATE VIEW [EmployeesView]
  as
  select *
  from [Employees] A
  union all
  select *
  from DB2.dbo.[Employees] B
  </code></pre>
  
  <p>Executing the views work as expected.  My problem is when I join the 2 views.</p>
  
  <pre><code>select C.CompanyName, E.CompanyName, E.EmployeeName 
    from CompaniesView as C
    join EmployeesView as E 
      on C.CompanyID = E.CompanyID
  </code></pre>
  
  <p>Here are the results:</p>
  
  <pre><code>CompanyName       CompanyName      EmployeeName
  ------------------------------------------------------------
  Disney            Disney           Donald Duck
  Disney            Disney           Mickey Mouse
  Warner Brothers   Warner Brothers  Wolverine
  Warner Brothers   Warner Brothers  Cyclops
  Marvel            Marvel           Spiderman
  Marvel            Marvel           Captain America
  Hanna Barbera     Hanna Barbera    Fred Flinstone
  Hanna Barbera     Hanna Barbera    Barney Rubble
  DC                DC               Superman
  DC                DC               Batman
  Warner Brothers   Disney           Donald Duck
  Warner Brothers   Disney           Mickey Mouse
  Warner Brothers   Warner Brothers  Wolverine
  Warner Brothers   Warner Brothers  Cyclops
  Hanna Barbera     Marvel           Spiderman
  Hanna Barbera     Marvel           Captain America
  Hanna Barbera     Hanna Barbera    Fred Flinstone
  Hanna Barbera     Hanna Barbera    Barney Rubble
  </code></pre>
  
  <p>These are the results I want:</p>
  
  <pre><code>CompanyName       CompanyName      EmployeeName
  ------------------------------------------------------------
  Disney            Disney           Donald Duck
  Disney            Disney           Mickey Mouse
  Warner Brothers   Warner Brothers  Wolverine
  Warner Brothers   Warner Brothers  Cyclops
  Marvel            Marvel           Spiderman
  Marvel            Marvel           Captain America
  Hanna Barbera     Hanna Barbera    Fred Flinstone
  Hanna Barbera     Hanna Barbera    Barney Rubble
  DC                DC               Superman
  DC                DC               Batman
  </code></pre>
  
  <p>How can I get this to work using using Views only?</p>