Select differents database for join - mysql

Question

I am trying to do a sql script to retrieve info from a main table and join them with data from a table in another database (Same server). The hard part is that for each row in the "main" table, the data that I have to join is in a different database.

I am not sure how I would proceed to do that, I think the only way would be to do some kind of loop. Am I right?

Here is how my two tables look like:

Main table:  
    main_id  
    database_name

other tables:  
    other_id  
    other_name

I'd like the result to be something like:

main_id  
database_name  
other_name

Edit: I am trying to write a stored procedure

How many "different" databases do you have and is this info available at design time? — PM 77-1, Jun 08 '16 at 18:40
Each row represent a database. It is a list clients and I need to go fetch the name of the client in its database. The number is unknown at design time as I could get more clients — Moustachauve, Jun 08 '16 at 18:49
@RyanVincent The question is not the same. The name of the database is inside a column of the main table. — Moustachauve, Jun 08 '16 at 19:15
Unless your table references just few "other" databases and all their names are known upfront, you are looking at creating a stored procedure. — PM 77-1, Jun 08 '16 at 19:27
@PM77-1 I should have said that I already am inside a stored proc. How would I proceed? — Moustachauve, Jun 08 '16 at 19:39

score 1 · Accepted Answer · answered Jun 08 '16 at 19:45

If you want to do that in mysql, you can (or rather: have to) use dynamic sql to generate an sql statement that has variable code (like database names) in it.

Try

set @stmt = 
  (select group_concat(concat(
                 'select * from main_table m ',
                 'join ',database_name,'.other_table o ', 
                 'on m.database_name = ''',database_name,''' ',
                 'and m.main_id = o.other_id ') 
                 SEPARATOR ' union all ')
   from (select database_name 
         from main_table 
         group by database_name) as dbdata);

prepare stmt from @stmt;
execute stmt;

This will first get all distinct database_name, then generate code that will join your entries from your main_table with the correct database.

It will not check if all databases exists, and the whole statement will fail if one doesn't, but you can of course check the databases first in information_schema beforehand.

score -1 · Answer 2 · answered Jun 08 '16 at 18:38

-1

You could use the "Union" option.

Or you could use temprary tables; insert from a select and later show the result.

Regards

answered Jun 08 '16 at 18:38

Daniel Azamar

672
7
8

I got it. The problem is "the tables" are in another database right?? You can use Select * from databaseName.table. – Daniel Azamar Jun 08 '16 at 18:39
1

Please re-read the question. The problem is that database names are dependent on the content of a primary database. – PM 77-1 Jun 08 '16 at 18:42
As @PM77-1 said, each row contains a database name as a varchar and I need to go fetch a name in that database with it. I don't think it is possible to do something like `INNER JOIN database_name.other_table` where `database_name` is the name of a column. – Moustachauve Jun 08 '16 at 19:21
Ok. You could use a structured procedure. Where you use "if then else" situation. Or you can use the solution: "prepare stmt from @stmt; execute stmt;" – Daniel Azamar Jun 09 '16 at 19:01
Ohh!! Right. You can use a "cursor". And for each row use an especific condition. For instance: http://stackoverflow.com/questions/5125096/for-loop-example-in-mysql – Daniel Azamar Jun 09 '16 at 19:02

Select differents database for join - mysql

2 Answers2