Join tables from different databases on same SQL server

Posted on 2008-11-05
Medium Priority
Last Modified: 2012-05-05
I have three tables, one from one database and two from another, both databases on the same SQL server:  (The server name is M2MSERVER)

I want a SELECT statement to select certain fields from each of the three tables, the common field being ITEM_NO. The three tables have no other common field.
Each m2mdata03.dbo.inmast record can have multiple matches in both INMASSINVTXN.dbo.INMASSTXNS and m2mdata03.dbo.inonhd.
The result would, in effect, join the two m2mdata03 tables first, creating a combination record for every ITEM_NO in inonhd that has a matching ITEM_NO record in inmast.
Then it would, in effect, join that result for every record in INMASSTXNS that has a matching ITEM_NO in the inmast/inonhd result.

I know how to make this work when it involves only two tables and both are in the same database, but not how to deal with three tables and two databases in an SQL SELECT and JOIN statement.

Thanks for the help!

Question by:Glenn Stearns
LVL 39

Accepted Solution

BrandonGalderisi earned 2000 total points
ID: 22887118
This is a select all, you can filter down the fields you want from there.

select * from m2mdata03.dbo.inmast t1
on t1.item_no = t2.item_no
join m2mdata03.dbo.inonhd t3
on on t1.item_no = t3.item_no

Author Closing Comment

by:Glenn Stearns
ID: 31513555
Thanks...as usual, I was trying too hard to write my script! Yours works great - really appreciate the quick reply!

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

839 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