Join tables from multiple databases with different user credentials

Question

i have a need to join two tables from different databases.
code will look just like this:

$tbl1 = 'table1';
$tbl2 = 'table2';

$DB1 = 'DB1';
$DB2 = 'DB2';

$connect1 = mysql_connect(DB_SERVER,DB_USER1,DB_PASSWORD1);
mysql_select_db ($DB1, $connect1);

$connect2 = mysql_connect(DB_SERVER,DB_USER2,DB_PASSWORD2);
mysql_select_db ($DB2, $connect2);
/* note both the DB are in the same server and users has privileges to theyr own datadases say usr1 to DB1, usr2 to DB2 */

mysql_query("SELECT * FROM $DB1.$tbl1 AS tb1 LEFT JOIN $DB2.$tbl2 AS tb2 ON tb1.id = tb2.id", $connect1);

the above query returns a error saying table2 is not present and it is obvious coz' im using $connect1

help me people!!

score 3 · Accepted Answer · answered May 18 '12 at 04:51

3

If each one of your two users only has access to one database and not the other, you will not be able to use both databases/tables in one query -- as no user will be able to run that query.

You'll either have to :

Get a user who has access to both databases -- and, as such, can run this query,
Or fetch data from each database/table with the user who has access to it -- and then work on those data from your PHP code to merge them (basically, doing the join in PHP, and not in SQL ; even if that's probably not the most efficient idea).

answered May 18 '12 at 04:51

Pascal MARTIN

395,085
80
655
663

my tables are too fat, i prefer your first suggestion of giving access to user on the other database. Thanks MARTIN, i appreciate it! – Mohan Babu May 18 '12 at 05:11
1

Third option would be having a DB connection from one database to another, in a way that there would be "view" in one database to the contents of another database. That view could then be joined with the actual table. I know how to do this with Oracle, but wouldn't know if it can be done with MySQL. I'd think it would be somehow possible. – eis May 18 '12 at 05:24
@eis, it would be very helpful if you provide me a code snippet or something to explain how it is performed in Oracle. – Mohan Babu May 18 '12 at 10:03
@MohanBabu added as an answer, since it would be harder to explain in comments. – eis May 18 '12 at 10:54

score 0 · Answer 2 · answered May 18 '12 at 04:56

0

You cannot join from two databases. Consider using one database whenever possible, it makes programming easier and boosts performance. There are ways to do it in PHP, but they are fairly complex and would be very bad for performance.

answered May 18 '12 at 04:56

Connor Peet

6,065
3
22
32

score 0 · Answer 3 · edited May 23 '17 at 12:20

At least with Oracle, third option to alternatives suggested earlier is to create a database link in DB1 which will make available some selected information from DB2.

The way it works is that you first create the DB link

-- as the current user
CREATE DATABASE LINK sales.us.americas.acme_auto.com USING 'sales_us';

-- as another user, shared public
CREATE SHARED PUBLIC DATABASE LINK sales.us.americas.acme_auto.com
CONNECT TO scott
IDENTIFIED BY tiger
AUTHENTICATED BY anupam
IDENTIFIED BY bhide USING 'sales';

Then you can query it like

SELECT * FROM target_table@sales.us.americas.acme_auto.com;

and also combine to queries for the "local" database. Also, a synonym could be used, if you don't want to type in the db link every time.

Here's a manual entry for Oracle DB links which contains some more examples and further clarification.

For MySQL, some alternatives have been discussed in this thread. Apparently there is no direct alternative.

Join tables from multiple databases with different user credentials

3 Answers3