• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 332
  • Last Modified:

Join two databaes in one query

I need a query that will join two databases and two tables.
I would like to have an example of the one that the databases are on the same server.
And one with the DB's on a different servers.

thanks
Sam

0
SamRunyon
Asked:
SamRunyon
  • 3
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
for dbs on the same server it is easy, u need to use  3 part naming convention,  databaseName.schema.Object  for the objects

SELECT a.*, b. *
FROM db1.dbo.Table1 a
inner join db2.dbo.Tab2 b on a.id = b.id


for objects across the servers, you need to create the linked server first and after that you can use the 4 part names to refer the objects
[linkedServerName].databaseName.schema.Object  
0
 
SamRunyonAuthor Commented:
example of what I want.

Select *, *
From AdventureWorks.Person.Contact
Inner join AdventureWorksDW.dbo.DimCustomer

gives this error
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'where'.

thanks, Sam

0
 
SharathData EngineerCommented:

Mention some JOIN condition and remove the extra * in the SELECT clause.
Select *
From AdventureWorks.Person.Contact
Inner join AdventureWorksDW.dbo.DimCustomer
on 1 = 1

If you don't have any condition, you can use CROSS JOIN.
Select *
From AdventureWorks.Person.Contact
cross join AdventureWorksDW.dbo.DimCustomer
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
SamRunyonAuthor Commented:
What does the "on 1 = 1"  mean?
by the way this works.

0
 
SharathData EngineerCommented:

for INNER,LEFT and RIGHT JOINs you need to mention some JOIN condition with ON clause. i kept 1=1 as JOIN condition to avoid syntax errors.
replace that with your actual JOIN condition like
Select *
From AdventureWorks.Person.Contact as t1
Inner join AdventureWorksDW.dbo.DimCustomer as t2
on t1.ContactID = t2.CotactId -- something like this
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
what was the issue with my post ;)
0
 
SamRunyonAuthor Commented:
aneeshattingal: there was nothing wrong with your post.

the help was great.

Sam
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now