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
