Page 1 of 1

Getting values from multiple tables

Posted: Tue Aug 23, 2016 3:42 am
by 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?

Re: Getting values from multiple tables

Posted: Tue Aug 23, 2016 2:16 pm
by 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.

Re: Getting values from multiple tables

Posted: Wed Aug 24, 2016 2:10 am
by xiaochris
Hi Aleksey,

thanks for the reply.

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

Re: Getting values from multiple tables

Posted: Wed Aug 24, 2016 12:23 pm
by 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.