Join Between Tables in Two Different Databases

Join between tables in two different databases?

Yes, assuming the account has appropriate permissions you can use:

SELECT <...>
FROM A.table1 t1 JOIN B.table2 t2 ON t2.column2 = t1.column1;

You just need to prefix the table reference with the name of the database it resides in.

Joining Two SQL Tables on Different Databases

Create a mapping table to bridge between the different codes for fruits.

IF OBJECT_ID('tempdb..#FruitMappings') IS NOT NULL
    DROP TABLE #FruitMappings

CREATE TABLE #FruitMappings (
    Table1Fruit VARCHAR(100),
    Table2Fruit VARCHAR(100))

INSERT INTO #FruitMappings (
    Table1Fruit,
    Table2Fruit)
VALUES
    ('Apples', 'Savory'),
    ('Oranges', 'Citrusy'),
    ('Bananas', 'Mealy')

SELECT
    T1.*
    --, whichever columns you need
FROM
    Database1.Schema1.Table1 AS T1
    INNER JOIN #FruitMappings AS F ON T1.Fruit = F.Table1Fruit
    INNER JOIN Database2.Schema2.Table2 AS T2 ON 
        F.Table2Fruit = T2.Fruit AND
        T1.Cost = T2.Cost AND
        T1.Duration = T2.Duration
        -- AND any other matches you need

You can use LEFT JOIN or even FULL JOIN, depending if you might have some fruits on a table that aren't available on the other (careful with NULL values if FULL JOIN).

Querying data by joining two tables in two database on different servers

You'll need to use sp_addlinkedserver to create a server link. See the reference documentation for usage. Once the server link is established, you'll construct the query as normal, just prefixing the database name with the other server. I.E:

-- FROM DB1
SELECT *
FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
    INNER JOIN [DB2].[MyDatabaseOnDB2].[dbo].[MyOtherTable] tab2
        ON tab1.ID = tab2.ID

Once the link is established, you can also use OPENQUERY to execute a SQL statement on the remote server and transfer only the data back to you. This can be a bit faster, and it will let the remote server optimize your query. If you cache the data in a temporary (or in-memory) table on DB1 in the example above, then you'll be able to query it just like joining a standard table. For example:

-- Fetch data from the other database server
SELECT *
INTO #myTempTable
FROM OPENQUERY([DB2], 'SELECT * FROM [MyDatabaseOnDB2].[dbo].[MyOtherTable]')

-- Now I can join my temp table to see the data
SELECT * FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
    INNER JOIN #myTempTable tab2 ON tab1.ID = tab2.ID

Check out the documentation for OPENQUERY to see some more examples. The example above is pretty contrived. I would definitely use the first method in this specific example, but the second option using OPENQUERY can save some time and performance if you use the query to filter out some data.

Inner join in 2 different databases

You just need to change that query a bit

Select * from Database1.[dbo].Table1 tab1
join Database2.[dbo].Table2 tab2 on tab1.ID = tab2.ID

or

Select * from Database1..Table1 tab1
join Database2..Table2 tab2 on tab1.ID = tab2.ID

Change names of databases, tables and IDs and give it a go

You can use it in all of connections on your server (every database), as your query have reference to databases

Joining 3 tables from two different Databases?

I think maybe this is what you want?

SELECT 
  coordinate_id, 
  section_name, 
  x_coord, 
  y_coord, 
  wsu.username, 
  wsu.hostname, 
  w.pod 
FROM 
  sandbox_maesc4.coordinates c  
INNER JOIN
  softphone_materials_updater.workstations w
  ON c.station_number = w.pod
INNER JOIN
  sandbox_maesc4.workstation_userlogged wsu
  ON w.ws = wsu.ws

Not sure about the database and table names, they seem to differ between your sample query and the description.

---

Related Topics