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
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to