From: Claudio Nanni
Date: November 21 2008 8:46pm
Subject: Re: SELECT through many databases
List-Archive: http://lists.mysql.com/mysql/215334
Message-Id: <49271E2A.7040509@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I would define a view out of the union query, you will only have to 
change that view whenever you add a database to your instance.
Seems a bit strange that you could not manage this scenario, do you have 
so many databases added?
If you really do not want to put your hands on code any more the only 
way to proceed,
remaining on mysql environment, is to use metadata, that is 
information_schema.
for instance the following statement gives you all the tables in your 
mysql instance:

mysql> select TABLE_SCHEMA,TABLE_NAME from information_schema.TABLES 
where TABLE_NAME='your_table';
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| db1          | your_table   |
+--------------+------------+
| db2          | your_table   |
+--------------+------------+
| db3          | your_table   |
+--------------+------------+
| db4          | your_table   |
+--------------+------------+
4 row in set (0.01 sec)
mysql>

Anyway you need a minimum a procedure programming to use this data and 
get your query out of this.

Aloha

Claudio Nanni



Andre Matos wrote:
> Hello,
>
> Let's suppose I have 5 database: db1, db2, db3, db4, and db5. They all 
> have the same structure but different data.
>
> I would like perform this select
>
> SELECT TaskDoneOn, TaskDoneBy
> FROM {database}
> WHERE TaskDoneOn IS NOT NULL
>
> and collect the data from all 5 database. However, I would like to 
> avoid doing something like this:
>
> SELECT TaskDoneOn, TaskDoneBy
> FROM db1
> WHERE TaskDoneOn IS NOT NULL
> UNION
> SELECT TaskDoneOn, TaskDoneBy
> FROM db2
> WHERE TaskDoneOn IS NOT NULL
> UNION
> SELECT TaskDoneOn, TaskDoneBy
> FROM db3
> WHERE TaskDoneOn IS NOT NULL
> UNION
> SELECT TaskDoneOn, TaskDoneBy
> FROM db4
> WHERE TaskDoneOn IS NOT NULL
> UNION
> SELECT TaskDoneOn, TaskDoneBy
> FROM db5
> WHERE TaskDoneOn IS NOT NULL
>
>
> Today I have 5, but tomorrow I can have 50 and I don't want to forget 
> any database.
>
> Thanks for any help.
>
> Andre
>
>
>
>
>