How to Use Join For Two Different Database Tables

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

MySQL join select on two different database tables

Your query involves 2 id columns one from inner query and second from partner_company_clients so using select id it isn't clear enough which id column you want so use alias before the column name or use complete db and table name before columns

SELECT mac.id, pcc.phone 
....

or

SELECT mac.id, b_database.partner_company_clients.phone 
....

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

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.

Join two tables from different database of different server in laravel

You can do so easily with Eloquent models. It is not proper SQL joins but you can achieve relationship querying across multiple database or event servers.

1. In your config/database.php file, declare as much connection as needed
2. Create the models you want for each table
3. Specify the $connection attribute in the models

For example:

class Model1 extends Model
{
    public $connection = 'mysql_database';
    
    public function model2()
    {
        return $this->belongsTo(Model2::class);
    }
}

class Model2 extends Model
{
    public $connection = 'postgre_database';
    
    public function model1s()
    {
        return $this->hasMany(Model1::class);
    }
}

You can then use the relations between those models as normal Eloquent relationship

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