Using a Right Outer Join to Match Records from Two Different Databases

Question

SQL 2005:

I am trying to create an outer join that will pull records from two different databases. My objective is to determine which records in database B don't have matching records in database A. When I tried running the query, it returned the error below. I am not sure how to get around this error:

'Tables or functions 'AssetCompType_EquipmentProperty_LinkTable' and 'TA-Reporting.dbo.AssetCompType_EquipmentProperty_LinkTable' have the same exposed names. Use correlation names to distinguish them.'

          select * 
            from AssetCompType_EquipmentProperty_LinkTable
right outer join [database A].dbo.AssetCompType_EquipmentProperty_LinkTable on
[database A].dbo.AssetCompType_EquipmentProperty_LinkTable.AssetCompTypeID=
[database B].dbo.AssetCompType_EquipmentProperty_LinkTable.AssetCompTypeID

Answer 1

Just assign aliases:

 select * 
            from AssetCompType_EquipmentProperty_LinkTable tbl_thisDB
right outer join [database A].dbo.AssetCompType_EquipmentProperty_LinkTable tbl_A on
tbl_A.AssetCompTypeID=
tbl_thisDB.AssetCompTypeID

Answer 2

You need to use table aliases when JOINing tables with identical names, if not themselves:

          SELECT a.*, b.*
            FROM AssetCompType_EquipmentProperty_LinkTable a
RIGHT OUTER JOIN [database A].dbo.AssetCompType_EquipmentProperty_LinkTable b ON a.AssetCompTypeID = b.AssetCompTypeID

It's necessary for the JOIN syntax alone, but additionally because you can't use SELECT * because of the likelihood of identical columns in the tables as well.

That said - to get the result you desire, you should consider:

Using NOT IN

---

SELECT b.*
  FROM [database A].dbo.AssetCompType_EquipmentProperty_LinkTable b
 WHERE b.AssetCompTypeID NOT IN (SELECT a.AssetCompTypeID
                                   FROM AssetCompType_EquipmentProperty_LinkTable a)

Using NOT EXISTS

---

SELECT b.*
  FROM [database A].dbo.AssetCompType_EquipmentProperty_LinkTable b
 WHERE NOT EXISTS (SELECT NULL
                     FROM AssetCompType_EquipmentProperty_LinkTable a
                    WHERE a.AssetCompTypeID = b.AssetCompTypeID) 

Using LEFT JOIN/IS NULL

---

   SELECT b.*
     FROM [database A].dbo.AssetCompType_EquipmentProperty_LinkTable b
LEFT JOIN AssetCompType_EquipmentProperty_LinkTable a ON a.AssetCompTypeID = b.AssetCompTypeID
    WHERE a.AssetCompTypeID IS NULL

Conclusion

---

Of the three options, the NOT IN and NOT EXISTS are equivalent - the LEFT JOIN/IS NULL is less efficient. See this article for more details.

Answer 3

It appears you must differentiate between the tables. Try with:

select * 
from AssetCompType_EquipmentProperty_LinkTable T1
  right outer join 
    [database A].dbo.AssetCompType_EquipmentProperty_LinkTable T2 
       on T1.AssetCompTypeID = T2.AssetCompTypeID