List:General Discussion« Previous MessageNext Message »
From:Rick James Date:July 30 2013 10:28pm
Subject:RE: Question regarding creating a query
View as plain text  
> 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.sk@stripped]
> Sent: Tuesday, July 30, 2013 11:13 AM
> To: mysql@stripped
> 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
Thread
Question regarding creating a querySukhjinder K. Narula30 Jul
  • RE: Question regarding creating a queryRick James30 Jul
  • Re: Question regarding creating a queryhsv31 Jul