Joining Two SQL Tables on Different Databases

Question

I have two tables on two separate databases. We will call them Table1 and Table2.

Table1:

+----------+----------+----------+---------+------+----------+
| UniqueID |  Date1   |  Date2   |  Fruit  | Cost | Duration |
+----------+----------+----------+---------+------+----------+
|        1 | 09/10/18 | 10/20/18 | Apples  | 1.50 |        7 |
|        2 | 09/18/18 | 10/25/18 | Oranges | 1.75 |       10 |
|        3 | 10/01/18 | 10/30/18 | Bananas | 2.00 |       10 |
+----------+----------+----------+---------+------+----------+

Table2:

+----------+---------+------+----------+-----------+
|  Date1   |  Fruit  | Cost | Duration | New Price |
+----------+---------+------+----------+-----------+
| 09/10/18 | Savory  | 1.50 |        7 |      1.90 |
| 09/18/18 | Citrusy | 1.75 |       10 |      2.50 |
| 10/01/18 | Mealy   | 2.00 |       10 |      2.99 |
| 10/20/18 | Savory  | 1.50 |        7 |      3.90 |
| 10/25/18 | Citrusy | 1.75 |       10 |      4.50 |
| 10/30/18 | Mealy   | 2.00 |       10 |      5.99 |
+----------+---------+------+----------+-----------+

What I need the output to look like:

+----------+----------+--------------------+----------+--------------------+
| UniqueID |  Date1   | New Price on Date1 |  Date2   | New Price on Date2 |
+----------+----------+--------------------+----------+--------------------+
|        1 | 09/10/18 |               1.90 | 10/20/18 |             3.90   |
|        2 | 09/18/18 |               2.50 | 10/25/18 |               4.50 |
|        3 | 10/01/18 |               2.99 | 10/30/18 |               5.99 |
+----------+----------+--------------------+----------+--------------------+

I need to first convert table1.fruit to the representation of table2.fruit (apples-->savory, oranges-->citrusy, bananas-->mealy) then join on table1.fruit = table2.fruit, table1.duration = table2.duration, table1.cost = table2.cost, table1.date1 = table2.date1, and table1.date2 = table2.date1.

I don't know where to start on writing the statement. I looked over some previous questions posted here, but they really just go over the basics of linking two tables from different databases. Do I convert the table1.fruit first in the select statement, then join, or do I convert table1.fruit in the join statement? How do I join table2.date1 on both table1.date1 and table1.date2 to get the price associated with both dates?

If I can provide any more information for you, please let me know.

I am on SQL Server 2017 using Management Studio.

Thanks for any help in advance!

Use the three-part name of each table, eg `db1.dbo.Table1 inner join db2.dbo.Table2` — Panagiotis Kanavos, Jan 08 '19 at 16:27
the problem seems to be less about the fact they're on different databases than the fact you haven't got any fields in common? Even if the tables were in the same database you'd have the same problem with the data. I think you need a lookup table which provides a mapping from one string to the other, and use that as an intermediary in your join. The actual syntax for using tables from two databases is trivial, as Panagiotis has shown (and is also very easy to look up online already) — ADyson, Jan 08 '19 at 16:29

score 2 · Answer 1 · answered Jan 08 '19 at 16:31

If both databases are on the same SQL Server instance and your SQL Server login has access to both databases you can just use the full form of the object names:

select * -- Whatever...
from Database1.dbo.Table1 t1
  inner join Database2.dbo.Table2 t2
    on t1,UniqueId = t2.UniqueId -- Or whatever your join condition is

(adding where etc. clauses as required.)

This assumes both databases are using the default schema, otherwise replace dbo as necessary.

If the databases are on different servers you can use linked servers, but there are performance implications (the whole remote table may be read because the optimiser can't do much to filter it).

score 2 · Accepted Answer · answered Jan 08 '19 at 16:35

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

`temp`-table is not needed in general case. You can replace CREATE-INSERT with aliased subselect or table expression `WITH FruitMappings AS` (body of your INSERT) — Alex Yu, Jan 08 '19 at 16:43
Yes, I'm just showing how to do the join. I believe the mappings should stay in a physical table as I'm assuming that there will be many of them and want to actually preserve the mappings used. — EzLo, Jan 08 '19 at 16:46
`IF OBJECT_ID `-DROP-CREATE-INSERT are obviously redundant for such simple case. — Alex Yu, Jan 08 '19 at 16:57

Joining Two SQL Tables on Different Databases

2 Answers2