2

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.
John
  • 10,154
  • 9
  • 79
  • 143

2 Answers2

2

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;
M Khalid Junaid
  • 60,231
  • 8
  • 78
  • 110
  • 1
    Better to send one command instead of three commands: ```EXECUTE IMMEDIATE``` (MariaDB > 10.2.3) – Georg Richter Aug 18 '20 at 13:18
  • I got it working and I appreciate the extra tidbits (`PREPARE`, `EXECUTE` and `DEALLOCATE`) in an example that isn't convoluted. – John Aug 18 '20 at 13:37
  • @GeorgRichter Could you please clarify what would `EXECUTE IMMEDIATE` replace in M's answer? I'm always for better performance if the reliability remains the same. – John Aug 18 '20 at 13:40
  • @GeorgRichter Nevermind, the manual actually addresses a real world scenario for once: https://mariadb.com/kb/en/execute-immediate/. – John Aug 18 '20 at 13:47
1

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');
Georg Richter
  • 2,628
  • 1
  • 6
  • 10
  • Thank you, I was already hard at work adapting what I got to work. I've documented *both* in my reference. – John Aug 18 '20 at 14:11