?
Solved

Join two databaes in one query

Posted on 2009-05-05
7
Medium Priority
?
323 Views
Last Modified: 2012-05-06
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
Comment
Question by:SamRunyon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24309783
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
 

Author Comment

by:SamRunyon
ID: 24310203
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24310231

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:SamRunyon
ID: 24310275
What does the "on 1 = 1"  mean?
by the way this works.

0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 24310287

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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24310496
what was the issue with my post ;)
0
 

Author Comment

by:SamRunyon
ID: 24310515
aneeshattingal: there was nothing wrong with your post.

the help was great.

Sam
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question