Solved

Join two databaes in one query

Posted on 2009-05-05
7
311 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
  • 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 40

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

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

0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

830 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