From: Claudio Nanni (claudio.nannigmail.com)
Date: Fri Nov 21 2008 - 14:46:34 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]