Search code examples
mysqlsqljoinsubqueryinnodb

MySQL subquery or join different databases where database name is in the main query's result set

For example, I need to list all customers and count their users in this simplified self explanatory SQL:

SELECT customer_name,
       (SELECT COUNT(*) FROM <main_db.customers.database_name>.users) AS user_count
FROM main_db.customers

Let's say this is main_db structure:

+--------------------------------+
| customer_name  | database_name |
|--------------------------------|
| Customer One   | customer_db1  |
| Customer Two   | customer_db2  |
| Customer Three | customer_db3  |
| etc...                         |
+--------------------------------+

And this is customer_dbX structure:

+------------+
| users      |
|------------|
| User One   |
| User Two   |
| User Three |
| etc...     |
+------------+

I want to receive this result set:

+-----------------------------+
| customer_name  | user_count |
|-----------------------------|
| Customer One   | 12         |
| Customer Two   | 59         |
| Customer Three | 34         |
| etc...                      |
+-----------------------------+

Is this possible with a subquery, join or ANY syntax?

Solution

  • This is too long for a comment.

    No, you cannot do what you want as a single query. You can use dynamic sql making use of the information_schema.schemata table.

    But, perhaps there are other solutions. First, I would recommend that you not use separate databases for different customers, unless you absolutely have to. Here are a few reasons why you have to:

    • You are contractually obligated to use separate databases (perhaps because lawyer's don't fully understand database security).
    • The databases have different backup/restore requirements.
    • Different customers will have customer-specific customizations that are most easily handled as different databases.

    Under most circumstances, storing data for multiple customers in a single database is the right way to go. It certainly simplifies managing the system, upgrading to new versions, identifying and fixing bugs, backing up the database, replicating the system in case of failure, and so on and so on.

    If, though, you have to have separate databases, then consider creating a view in the master database that combines all the tables together:

    create view v_master_users as
    select 'x' as which, d.* from customer_db<X> d union all
    select 'x1' as which, d.* from customer_db<x> d union all
    . . .;

    Then, use this view for your querying.

    If adding a customer requires creating a database, then you'll have ample opportunity to update the view to handle new customers.