Getting values from multiple tables

[xiaochris]

Hi,

I am trying to get columns from multiple tables and displaying values in the report. How may i do this?

e.g.

Tables
asset1
asset2
asset3
asset4
.
.
.
asset9999

Note: These tables will keep on increasing.

Currently, I have a query which is able to retrieve all the tables

SHOW TABLES FROM `assetlist` WHERE `assetlist` LIKE 'asset%'

Now my problem is, how do i get the values from all these tables?

[Alex K.]

Hello,

Unfortunately, directly in the designer it not possible.
In this case, you can prepare a query as:
select * from asset1
union
select * from asset2
...
and then you can use it in the designer.

Thank you.

[xiaochris]

Hi Aleksey,

thanks for the reply.

could u provide an example of how i can pass the prepared query over to the designer?

[Alex K.]

Hello,

You can use something like this:

Code: Select all

...
string query = "";
foreach (DataRow row in ds.Tables["TablesList"].Rows)
{
    query += "select * from " + row["TableName"].ToString() + " union ";
    ...
}

Thank you.