Query Multiple table with same structure

Question

I have a few tables with the same structure built and wanted to query them at once using PHP MYSQL. The challenging part that I face was to query x tables where I do not know how many of them there. (Assuming user will select the tables on UI perspective). The reason of this is to output them into a csv format.

For example:-

Date, xTable, yTable, zTable, ....
==============================b
bla      1        2      3
bla      4        3      5
...
...

How can I modify below query to cater for above needs? (The query needs to be flexible enough to query multi table).

SELECT created_dt, xTable FROM needToQueryMultiTable WHERE created_dt BETWEEN ? AND ?

Answer 1

You can create a view of all possible tables:

create v_tables as
    select 'table1' as which, t.*
    from table1 t
    union all
    select 'table2' as which, t.*
    from table2 t
    . . .;

Then, you can select from the view.

In MySQL this is not particularly efficient, because all the tables will still need to be read. However, for smaller tables this could be a reasonable solution.

You should ask yourself why you have identically structured tables in the databases. In general, these should be combined into a single table. For performance, you can partition the table.