suppose that i have the following table in my database called 'central':

The customer column references to the 'Customers' table which is inside of another database.

IMPORTANT: the database names A1 and A2 are only examples. There will be hundreds of separate databases containing a Customers table. The central.DB column is a reference to which database to use when selecting the CustomerName from inside of the select statement that you see below the following example databases.

An example from the Customers table inside of Database 'A1':

An example from the Customers table inside of Database 'A2':

I can't figure out how to do something like this and maybe it's not possible at all:

SELECT Central.id, Central.Name, (central.DB).Customers.CustomerName 
FROM central, (central.DB).Customers. 

Or maybe use a join to do this?

Either way, the query result should should be:

Maybe this isn't possible and the result is that i must do it with php? But i'd rather not do this as central.DB could reference potentially hundreds of databases and that will possibly be rather expensive (a lot of calls to the referenced databases).

(I can't change the setup of using a central database and the other separate databases)

Is it possible to reference to another database(table) when the name of that database is inside of the result set?

CodePudding user response：

If both databases are at the same server(MySQL Instance) you can just prefix the tables with the database name.

SELECT C.id, C.Name, CU.CustomerName FROM database_1.Central AS C LEFT JOIN database_2.Customers AS CU

Make sure that the user logged in has permission to access both databases.

CodePudding user response：

SELECT t0.id, 
       t0.name, 
       COALESCE(t1.CustomerName, t2.CustomerName) CustomerName
FROM my_database.central t0
LEFT JOIN A1.Customers t1 ON (t0.customer, t0.DB) = (t1.ID, 'DB1')
LEFT JOIN A2.Customers t2 ON (t0.customer, t0.DB) = (t2.ID, 'DB2')