Hello,

I have a question regarding creating a query as follows:

I have several databases (all with same structure), which I to query. For
instansce:

db1, db2, db3 - all have table tb1 with field a, b and table tb2 with
fields flag1, flag2

So I want to query and get field a from tb for all db's. One way to do is
union i.e.

SELECT a FROM db1.tb1 WHERE (SELECT flag1 FROM db1.tb2) = 'y'
UNION
SELECT a FROM db2.tb1 WHERE (SELECT flag1 FROM db2.tb2) = 'y'
UNION
SELECT a FROM db3.tb1 WHERE (SELECT flag1 FROM db3.tb2) = 'y'

But the problem here is that if I add more db's, I have to update the query
every time.

In addition to above, I also have a database e.g. common, which has a table
called dbnames with field name, that keeps the name of all the databases I
have (db1, db2, db3).

So, what I would like to do is query the common db to get the names of the
db's and then run the select query on each db.

So here is the pseudocode of what I want to do:


for each (SELECT name AS DbName FROM common.dbnames)

(SELECT a FROM DbName.tb1 WHERE (SELECT flag1 FROM DbName.tb2) = 'y') AS
CONCAT(DbName, '-', a)


Could you please advice if this possible and if yes, how can this be
acheived.

Many Thanks,

SK