Correlation Names

Question

Correlation Names

Tharg

SSC Eights!

Points: 980
More actions
January 27, 2010 at 11:00 am

#156616

SELECT AgentIP
FROM ConfigDB.dbo.NODES
RIGHT OUTER JOIN MainDB.dbo.NODES
ON
AgentIP = IP_ADDRESS
Msg 1013, Level 16, State 1, Line 2
The objects "MainDB.dbo.NODES" and "ConfigDB.dbo.NODES" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
Google not helping here...is anyone familiar with JOINS with two databases where the column names are the same?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ray K SSC-Dedicated Points: 31163 More actions · Answer 1

Found this: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64359

It means you haven't assigned aliases to your join objects. I didn't see any alias on your join that indicates what column belongs to what table.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Tharg SSC Eights! Points: 980 More actions · Answer 2

Thanks for the reply!

I understand the idea here but HOW do I assign aliases?

Ray K SSC-Dedicated Points: 31163 More actions · Answer 3

Try this:SELECT AgentIP

FROM ConfigDB.dbo.NODES CDB

RIGHT OUTER JOIN MainDB.dbo.NODES MDB

ON

CDB.AgentIP = MDB.IP_ADDRESS

Note: I'm guessing on where to put CDB and MDB on AgentIP and IP_ADDRESS, because I have no idea where they are. If I don't know, how will your code?

Explanation: I'm assigning an alias of "CDB" to your ConfigDB.dbo.NODES table, and "MDB" to MainDB.dbo.NODES. The nice thing about aliases is that it saves you from having to type the entire [DB].dbo.

format.

To do the join, SQL needs to know where AgentIP and IP_ADDRESS are located. CDB.AgentIP tells SQL, "the column AgentIP is located in the table that I'm calling CDB."

Hope this helps!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Tharg SSC Eights! Points: 980 More actions · Answer 4

Thanks!

Now I have the following which, I believe, is unresolvable:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Ray K SSC-Dedicated Points: 31163 More actions · Answer 5

Ser Tharg (1/27/2010)
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Argh . . . looks like your two tables are using different collations!

There probably is a way to resolve this, but it goes beyond my scope of knowledge. (All my tables and databases use the same collation, so this is not an issue I have to deal with.)

My only suggestion would be to look up collations in either BOL or MSDN.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Tharg SSC Eights! Points: 980 More actions · Answer 6

The below works!

SELECT AgentIP

FROM SCConfigMgmt.dbo.NODES CDB

RIGHT OUTER JOIN NetperfmonSC.dbo.NODES MDB

ON

CDB.AgentIP COLLATE Latin1_General_CI_AS = MDB.IP_ADDRESS

Ray K SSC-Dedicated Points: 31163 More actions · Answer 7

Kewl! Well done.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/