sql joining

how do you join between tables from different databases?
amillyardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sammySeltzerCommented:
that's a loaded question, in my opinion.

First, you should have a common key amongst the tables you are planning to join.

Second, you would prefix the db for each table like:

select db1.table1.field1, db1.table1.field2, db2.table2.field1, etc

from db1.table1, db2.table2
where db1.table1.key = db2.table2.key

That's basically it.

You could join more tables from other tables.

You can probably give more information including tablename, databases involved and keys, etc

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
amillyardAuthor Commented:
the below script works (when not adding in the database name) -- i.e. removing the DB2 (if table was on same DB as other table ... but when adding the second DB reference get the error:

'Invalid Object Name:   DB2.DBTB1

SELECT T1.[Index_ID], T2.[DisplayName]
FROM [Table1] T1
INNER JOIN DB2.DBTB1 T2 ON T1.Index_ID = T2.Index_ID

0
Kevin CrossChief Technology OfficerCommented:
With SQL server it is [database].[schema].[table] so correction for the above is db2..table or db2.dbo.table2 - guess you figured it out, but just stating that for future readers in case it is not clear and in case your question came after accepting the above.

Kevin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.