MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:hsv Date:July 31 2013 12:57am
Subject:Re: Question regarding creating a query
View as plain text  
>>>> 2013/07/30 14:12 -0400, Sukhjinder K. Narula >>>>
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
union i.e.

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
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....

Question regarding creating a querySukhjinder K. Narula30 Jul
  • RE: Question regarding creating a queryRick James30 Jul
  • Re: Question regarding creating a queryhsv31 Jul