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

SamRunyonAsked:
Who is Participating?
 
SharathConnect With a Mentor Data 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:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
SamRunyonAuthor Commented:
What does the "on 1 = 1"  mean?
by the way this works.

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

From novice to tech pro — start learning today.