Solved

Join two databaes in one query

Posted on 2009-05-05
7
318 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How can I exclude some wording in a like statement? 39 83
Need help how to find where my error is in UFD 6 52
tempdb log keep growing 7 56
SQL DATE Past due to current wek 4 22
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

751 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