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 |
+------------------+--------------+---------------------------------+
SQL Server allows you to join tables from different databases as long as those databases are on the same server. The join syntax is the same; the only difference is that you must fully qualify table names.
Yes, assuming the account has appropriate permissions you can use: SELECT <...> FROM A. table1 t1 JOIN B.
The Get External Data - Access Database import and link wizard opens. In the File name text box, type the name of the source database or click Browse to display the File Open dialog box. Click Link to the data source by creating a linked table, and then click OK. The Link Tables dialog box opens.
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';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With