1

This may not be possible ;^)

I'm trying to pull together billing and performance data from separate client dbs. The "core" db has a table like so:

client_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
client_db_name VARCHAR(100), 
...

Each client_db has a table, orders, with columns order_date and order_total.

Conceptually, I'd like to be able to use the client_db_name in a query:

SELECT  SUM(order_total) AS sales
FROM    {client_db_name}.orders AS o
WHERE   o.order_date LIKE '2021%'

Questions:

  1. is this even possible?
  2. if so, how would I JOIN this query? What would the ON be?

Many thanks!

Dustin Boswell
  • 31
  • 3
  • possible duplicate of this https://stackoverflow.com/questions/20611310/query-across-multiple-databases-on-same-server – Salvino D'sa Aug 29 '21 at 14:53
  • This [link](https://www.navicat.com/en/company/aboutus/blog/1059-how-to-query-across-multiple-databases) will be of great help I believe. – Salvino D'sa Aug 29 '21 at 14:55
  • Thanks, @Salvino, but neither of these actually speaks to the problem I'm trying to solve. As I said, it may be unsolveable given the architecture, because there's no way I've found to make the `{db_name}` component of `{db_name}.table_name.column_name` resolve dynamically. – Dustin Boswell Aug 29 '21 at 16:40
  • Can't you just interpolate the string value in the query with code and then execute the query? – Salvino D'sa Aug 29 '21 at 16:55

2 Answers2

0

No interpolation.

A Stored procedure might be able to perform the interpolation via CONCAT, PREPARE and EXECUTE.

Wouldn't it be better the have all the customers in the same table? That would make it all easier.

Rick James
  • 135,179
  • 13
  • 127
  • 222
0

You can make a dynamic query, a query that is the result of another query.

SELECT GROUP_CONCAT(query_piece SEPARATOR ' UNION ALL ')
FROM (SELECT CONCAT("SELECT '", client_db_name, "' AS client, SUM(order_total) AS sales FROM ", client_db_name, '.orders o WHERE YEAR(o.order_date) = 2021') AS query_piece
        FROM core.clients) AS sq

My query will return a dynamic query like this (I have two clients into core.clients table: a and b):

SELECT 'a' AS client, SUM(order_total) AS sales FROM a.orders o WHERE YEAR(o.order_date) = 2021 

UNION ALL 

SELECT 'b' AS client, SUM(order_total) AS sales FROM b.orders o WHERE YEAR(o.order_date) = 2021

The dynamic query will return something like this:

client sales
a 50.00
b 100.00
nachospiu
  • 2,009
  • 2
  • 8
  • 12