CODEWITHSUNDEEP
mysqlsql
Saint Robson
Saint Robson

Reputation: 5525

How To Left Join 2 Tables On 2 Different Databases?

I have first database (dbA) with table like this, named Username :

+------------------+--------------+
| Username         | PhoneNumber  |
+------------------+--------------+
| jamesbond007     | 555-0074     |
| batmanbegins     | 555-0392     |
+------------------+--------------+

then, on the other side, I have dbB with table like this, named PrivateMessage :

+------------------+---------------------------------+
| Username         | Message                         |
+------------------+---------------------------------+
| jamesbond007     | I need new bond-girl            |
| batmanbegins     | thanks for the paycheck, Nolan  |
+------------------+---------------------------------+

now, how to combine this two tables from 2 different databases so the output will look like this :

+------------------+--------------+---------------------------------+
| Username         | PhoneNumber  | Message                         |
+------------------+--------------+---------------------------------+
| jamesbond007     | 555-0074     | I need new bond-girl            |
| batmanbegins     | 555-0392     | thanks for the paycheck, Nolan  |
+------------------+--------------+---------------------------------+

Upvotes: 17

Views: 42206

Answers (4)

LSerni
LSerni

Reputation: 57418

Same as you would a normal table, except specifying the database:

SELECT dbA.Username, dbA.PhoneNumber, dbB.Message
   FROM dbA.Username LEFT JOIN dbB.PrivateMessage
   ON (dbA.UserName.Username = dbB.PrivateMessage.Username);

Things to look out for:

  • LEFT JOIN will return all users, also those with no messages (use INNER JOIN to retrieve only users with messages)
  • Users with multiple messages will appear multiple times (use aggregations and GROUP BY to only retrieve one message per user - you'll have to supply a criterion to choose the one message)
  • You need query privileges on both databases (otherwise some user with privileges on both has to copy, e.g. periodically in crontab, a table or a subset of a table from a database to the other)
  • Collations might not match. If this is the case, you have to change collation on one of the two tables using either COLLATE or converting the field of one DB to the charset of the other with CONVERT: CONVERT(db.table.field USING Latin1), which will prevent using indexes thus decreasing performances. You can modify one of the two tables, but verify that you're not disrupting whatever query or application is using the ALTER'ed table (in a pinch, convert the whole database to well-tempered UTF8).
  • JOINs on text fields aren't very efficient even if you have INDEX on that in both tables; it would be better to have the Message table holding a unique, numeric userid to refer to the message owner. I understand that two different databases with different logics might not be conducive to this solution, but you could apply one of the above "tricks" ("copy a table or subset thereof") and export, periodically, a converted and ID'ed table from a DB to the other. That one periodical query would be expensive, but all subsequent JOINs would greatly benefit.

Test run

This creates two tables with the same structure in two different databases, and joins them while in a third database.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.30 openSUSE package

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE first_database;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE DATABASE second_database;
Query OK, 1 row affected (0.00 sec)

mysql> USE first_database;
Database changed
mysql> CREATE TABLE mytable ( x integer, t varchar(32) );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO mytable ( x, t ) VALUES ( 1, 'One in First Database' ), ( 2, 'Two in First Database' );
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> USE second_database;
Database changed
mysql> CREATE TABLE mytable ( x integer, t varchar(32) );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO mytable ( x, t ) VALUES ( 1, 'One in Second Database' ), ( 3, 'Three in Second Database' );
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> USE test;
Database changed
mysql> SELECT * FROM first_database.mytable LEFT JOIN second_database.mytable USING ( x );
+------+-----------------------+------------------------+
| x    | t                     | t                      |
+------+-----------------------+------------------------+
|    1 | One in First Database | One in Second Database |
|    2 | Two in First Database | NULL                   |
+------+-----------------------+------------------------+
2 rows in set (0.00 sec)

mysql>

Upvotes: 3

Naveen Jain
Naveen Jain

Reputation: 1

Try the below code

SELECT * FROM dbA.Username JOIN dbB.PrivateMessage USING(Username);

Upvotes: 0

John Woo
John Woo

Reputation: 263883

You can simply join the table of different database. You need to specify the database name in your FROM clause. To make it shorter, add an ALIAS on it,

SELECT  a.*,          -- this will display all columns of dba.`UserName`
      b.`Message`
FROM  dba.`UserName` a  -- or LEFT JOIN to show all rows whether it exists or not
      INNER JOIN dbB.`PrivateMessage` b    
         ON a.`username` = b.`username`

but some how, there are possiblities where-in a username won't have messages. In this case use LEFT JOIN if you want still to show all the records of dba.Username.

Reading from your comments, the tables have different collation. The work around on this is to specify COLLATE on your joined statements,

SELECT  a.*,          -- this will display all columns of dba.`UserName`
      b.`Message`
FROM  dba.`UserName` COLLATE latin1_swedish_ci a  
      LEFT JOIN dbB.`PrivateMessage` COLLATE latin1_swedish_ci b    
         ON a.`username` = b.`username`

you can change latin1_swedish_ci to whatever you want.

For more info on COLLATION, see this full list of

Character Sets and Collations in MySQL

If you have enough privilege to ALTER the tables, simply use this syntax to manually convert and match their collations,

ALTER TABLE tbl_name CONVERT TO CHARACTER SET latin2 COLLATE 'latin2_general_ci';

Upvotes: 18

Bjoern
Bjoern

Reputation: 16304

The SQL for this is rather easy...

SELECT A.Username, A.PhoneNumber, B.Message
FROM dbA.Username as A
INNER JOIN dbB.PrivateMessage as B ON A.Username = B.Username

...assuming you can access both databases within your connection.

If you cannot access them, you have to work on a different approach (like copying one table to the other database before querying or something similar).

Upvotes: 0

Related Questions