Join tables from different databases on same SQL server

Posted on 2008-11-05
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:glennes
    LVL 39

    Accepted Solution

    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:glennes usual, I was trying too hard to write my script! Yours works great - really appreciate the quick reply!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now