> I have to update the query every time. Therein lies the difficulty with the schema design.
You could write a stored procedure to locate all the tables (use information_schema.TABLES, etc) and build the UNION, and finally execute it. The SP would have something very remotely like the foreach you suggested. > -----Original Message----- > From: Sukhjinder K. Narula [mailto:narula...@gmail.com] > Sent: Tuesday, July 30, 2013 11:13 AM > To: mysql@lists.mysql.com > Subject: Question regarding creating a query > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
