MySQL Forums
Forum List  »  MyISAM

Re: Query multiple databases
Posted by: Rick James
Date: October 18, 2013 08:45AM

Let's parse this:
select ConnectIP from ( select concat_ws('.',TABLE_SCHEMA,TABLE_NAME) from information_schema.TABLES where TABLE_NAME = "access") as t2;

There is a subquery in the FROM clause:
select concat_ws('.',TABLE_SCHEMA,TABLE_NAME) from ...
that is selecting one column:
concat_ws('.',TABLE_SCHEMA,TABLE_NAME)
which is, by default, called
"concat_ws('.',TABLE_SCHEMA,TABLE_NAME)"

So, that is the only field available for the outer query to fetch.

To achieve your goal, you would need to create and execute 1300 SELECTs. (Hmmm... Smells like the schema was not well designed. Usually, having lots of similar databases is a 'mistake'.)

So, how to solve the problem? Build a stored procedure. It would
1. find the 1300 db.tbl pairs
2. loop through them (using a cursor):
3. build a SELECT, PREPARE and EXECUTE it.
This would give you 1300 resultsets; you may prefer to INSERT each SELECT's output into a temp table, then SELECT all of the temp table.

Options: ReplyQuote


Subject
Views
Written By
Posted
9249
October 17, 2013 04:07AM
Re: Query multiple databases
3546
October 18, 2013 08:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.