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
RE: Question regarding creating a query
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
Re: Question regarding creating a query
2013/07/30 14:12 -0400, Sukhjinder K. Narula 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) Well, you could build up the united query in a string and pass it to PREPARE -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
