Use a user-defined variable to dynamically name second database to JOIN tables with that second database

user_606371 0
Viewed 80

I need to JOIN two different databases which I've already accomplished statically for testing purposes only.

  1. The first database is already defined in the connection itself.
  2. The second database must be dynamically derived from information within the first database that must first be defined in a user-defined variable. To keep things simple I will simply refer to the second database name as example2.
  3. Statically defining the second database name works and all the JOINed data is successfully pulled however with my current comprehension if I did this on a live (or "production") server I'd have to run two separate queries on the same connection with some PHP code in between those two queries.
  4. I can not define the second database name without running a second query and the point is to avoid making multiple connections to the SQL server when I know there is a way to do it in a single query (if we do not count setting a user-defined variable a query).

Here is a static demonstration of what worked for me:

SELECT * FROM example1 AS e1 
INNER JOIN example2.accounts AS e2a ON (e2a.id = e1.accounts_id);

However the following does not work:

SET @db = 'example2';
SELECT * FROM example1 AS e1 
INNER JOIN @db.accounts AS e2a ON (e2a.id = e1.accounts_id);

How can I set and use a user-defined variable to dynamically define the second database's name and use it to dynamically JOIN the tables between the two databases?

  • All data is properly stored/formatted/etc.
  • I already have the user-defined variable working, I need help implementing it on the database-to-database JOIN.
  • This sort of relates to the fact that PHP has variable variables.
mariadb-10.5 mariadb mysql
2 Answers

In MySQL you can use Prepared Statements like

SET @db = 'example2';
SET @sql = CONCAT('SELECT * FROM example1 AS e1 
                   INNER JOIN ', @db , 
                   '.accounts AS e2a ON e2a.id = e1.accounts_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
user_8533600

To avoid unnecessary round trips between client and server in 1st answer, here is a 2 liner:

SET @db = 'example2';
EXECUTE IMMEDIATE CONCAT('SELECT * FROM example1 AS e1 INNER JOIN ', @db , '.accounts AS e2a ON e2a.id = e1.accounts_id');
user_69305010
Related