views:

146

answers:

3

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
+1  A: 

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
Matt Whitfield
+1  A: 

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.

OMG Ponies
Actually, EXCEPT is probably the easiest way to do it (when the table definitions match) though not always the most efficient.
RBarryYoung
@RBarryYoung: `EXCEPT` also isn't ANSI
OMG Ponies
Not true, EXCEPT ***is*** in the standard. I've got it right here in every edition of the standard that I have checked back to 2003 so far. I can give you references if you want.
RBarryYoung
@RBarryYoung: If it is, SQL Server is the only DB I'm aware of that implements it.
OMG Ponies
Yes, it is, so good for SQL Server :-). Fortunately it *is* a SQL Server question too.
RBarryYoung
@OMGPonies: `PostgreSQL` implements `EXCEPT` too (and it's only system of the major four than implements `EXCEPT ALL`). `Oracle` implements `MINUS`, which is just a synonym for `EXCEPT`.
Quassnoi
@Quassnoi: How does the performance stack up against the NOT IN, LEFT JOIN IS NULL, NOT EXISTS in the DBs that support it?
OMG Ponies
@OMGPonies: In `SQL Server`, it is some flavor of an `ANTI JOIN` chosen by the optimizer. In `Oracle` and `PostgreSQL`, it is a special operation shown as such in the query plan. Essentially, this is a `MERGE ANTI JOIN` over the sorted sources.
Quassnoi
+1  A: 

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
momobo