I have a question regarding creating a query as follows:
I have several databases (all with same structure), which I to query. For
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
SELECT a FROM db1.tb1 WHERE (SELECT flag1 FROM db1.tb2) = 'y'
SELECT a FROM db2.tb1 WHERE (SELECT flag1 FROM db2.tb2) = 'y'
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
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