I have a table called app_to_tables which is mapping between apps and table names, table names are around 20, so I cannot hard code them in the query. Here for simplicity I put just 2 table names (table_1, table_2). Table structure is the following:
+--------+------------+ | app_id | table_name | +--------+------------+ | 1 | table_1 | | 1 | table_2 | | 2 | table_1 | | 2 | table_2 | +--------+------------+
from the other hand I have another tables, which named as table_1, table_2, table_3. The schema for these tables are the same, and the content is the following:
table_1 +------------+--------+--------+ | date | app_id | signal | +------------+--------+--------+ | 2018-01-01 | 1 | sg | | 2018-01-01 | 1 | sg | | 2018-01-01 | 2 | sg | | 2018-01-01 | 2 | sg | | 2018-01-02 | 1 | sg | | 2018-01-02 | 1 | sg | +------------+--------+--------+ table_2 +------------+--------+--------+ | date | app_id | signal | +------------+--------+--------+ | 2018-01-01 | 1 | sg | | 2018-01-01 | 2 | sg | | 2018-01-01 | 2 | sg | | 2018-01-01 | 2 | sg | | 2018-01-02 | 1 | sg | | 2018-01-02 | 2 | sg | +------------+--------+--------+
I am trying to have an sql query outcome which will be the following:
The number of signals per each date per each table_name per each app_id, like the following:
+--------+------------+------------+-------+ | app_id | table_name | date | count | +--------+------------+------------+-------+ | 1 | table_1 | 2018-01-01 | 2 | | 1 | table_1 | 2018-01-02 | 2 | | 1 | table_2 | 2018-01-01 | 1 | | 1 | table_2 | 2018-01-02 | 1 | | 2 | table_1 | 2018-01-01 | 2 | | 2 | table_2 | 2018-01-01 | 3 | | 2 | table_2 | 2018-01-02 | 1 | +--------+------------+------------+-------+
My main trouble is how I can select a table names and then dynamically (without hardcoding the table names in the query) use that table names to count the number of signals.
Advertisement
Answer
My main trouble is how I can select a table names and then dynamically (without hardcoding the table names in the query) use that table names to count the number of signals.
Unfortunately, this is not possible in SQL. SQL requires that the query names all tables explicitly before the query is prepared. You can’t write any SQL query that dynamically joins to additional tables depending on what values it discovers as it scans through data during execution.
It makes no more sense to SQL than asking for code that calls some function, whose name is in fact the return value of the function. Like in PHP, you can call a function whose name is based on a variable. But you clearly could not call a function whose name is the result of the function you call.
$result = $result(); // this is a paradox
What you can do is generate a query based on the distinct tables you find named in your app_to_tables table.
SELECT DISTINCT table_name FROM app_to_tables;
This gives you a short list of table names (perhaps around 20 tables in your case). Then you must write code to build an SQL query as a string from this list.
Basically like the solution given by @ThomasG on this thread.