mysql - How To Left Join 2 Tables On 2 Different Databases?

Question

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  |
+------------------+--------------+---------------------------------+

Answer 1

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';