MariaDB JOIN tables from different databases based on column value

Question

How do I use a column value as the database name to JOIN two tables from those two different databases?

I have already successfully joined two tables between two databases with a statically defined (second) database name:

SELECT * 
FROM db1.table_a AS ta
INNER JOIN db2.table_b AS tb on (ta.db_table_name = b.user_id) 

However where db2.table_b is in that query I need to somehow have the db2 instead be a value from the first table in the first database; the table name will be statically defined. All of the kind-of-related threads were totally useless and wildly convoluted.

---

Details: there is one common database and all of the other databases represent the same application but for different accounts. In order for all of the users on all of the different accounts to be able to interact with each other (e.g. database_2.accounts.user.43 (DB->Table->Column->ID (43)) the common database (db1 above) must not only store the id of the user but also the name of the database that must be joined.

To help visualize things:

• Database: common
• Database: db2

---

SELECT id, database_name 
FROM common.table_a AS ct 
INNER JOIN [database_name].table_b AS dn ON (ct.user_id = [database_name].users.id)

Visually the data returned should look something like this:

+----------+------------+----------+
| database | account_id | username |
+----------+------------+----------+
| db1      | 1          | John     |
+----------+------------+----------+
| db2      | 1          | Sally    |
+----------+------------+----------+
| db3      | 43         | John     |
+----------+------------+----------+
| db4      | 1          | Sally    |
+----------+------------+----------+

Then the HTML output should look something like this:

• Comment from John from db1.
• Comment from Sally from db2.
• Comment from John from db3.
• Comment from Sally from db4.

I can worry about ensuring visually that John from db1 and John from db3 (and Sally from db2 and Sally from db4) all four of which are different people in real life are represented as so. It's the dynamic aspect of selecting them based on the value of the column's value that contains the database name to be used to JOIN is all that matters.

Answer 1

Do you have hundreds of databases? That would be a 'bad' design. To discuss further, please explain why you have so many.

If you don't have many databases, but you need to dynamically pick which db, again, poor design; let's discuss further.

If you must do one of those, the hide it in Stored Routines (as P.Salmon almost suggested; his code needs some polishing) or in an application library (PHP, Java, whatever).

Otherwise, wherever you can say table_a, you can replace that with db1.table_a. In fact, you can see MySQl doing that: EXPLAIN EXTENDED SELECT ...; SHOW WARNINGS; Example:

mysql> EXPLAIN EXTENDED SELECT province FROM canada; SHOW WARNINGS;
+----+-------------+--------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table  | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | canada | index | NULL          | province | 105     | NULL | 5484 |   100.00 | Using index |
+----+-------------+--------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

+-------+------+---------------------------------------------------------------------------------------+
| Level | Code | Message                                                                               |
+-------+------+---------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `world`.`canada`.`province` AS `province` from `world`.`canada` |
+-------+------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

In this example, the table canada was replaced by world.canada because world was the database.

Answer 2

All things being equal (and I bet they aren't) and assuming all schemas/dbs are on the same server you should be able to construct a simple dynamic sql statement.

so given

+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
+----+------+
2 rows in set (0.00 sec)

Where name serves as proxy for db name we can first select all distinct names and create a sql statement unioning all the tables from all the dbs. Something like this.

SET @SQL =
( 
SELECT  GROUP_CONCAT(GCSTRING)
FROM
(
SELECT 'A' AS GC,CONCAT('SELECT ID,NAME FROM USERS U1 ',JSTRING,' ',DBNAME,' AS  ',NAMEALIAS,'  ON ',NAMEPREFIX,'.',USTRING) GCSTRING
FROM
(
SELECT DISTINCT 'JOIN ' AS JSTRING,NAME DBNAME , 
        NAME AS NAMEALIAS, NAME AS NAMEPREFIX, 'TABLEB.USER_ID = UI.NAME UNION' USTRING 
FROM USERS
) S
) T
GROUP BY GC
)
;

SET @SQL = REPLACE(@SQL,',',' ');
SET @SQL = SUBSTRING(@SQL,1,LENGTH(@SQL) - 5);
SET @SQL = CONCAT(@SQL,';');
SELECT @SQL

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQL                                                                                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT ID NAME FROM USERS U1 JOIN  aaa AS  aaa  ON aaa.TABLEB.USER_ID = UI.NAME UNION SELECT ID NAME FROM USERS U1 JOIN  bbb AS  bbb  ON bbb.TABLEB.USER_ID = UI.NAME ; |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)