how to select records from multiple database in one query?

Question

I want to use multiple database because I have 1 database which contain 40 tables and it doesnt seem good.Thats why I want to create another database but problem is I am not able to join this databases at one query for example I have 'core' database and it contains users table which has user_id column second database is post database and I need get user_id from users table which is in 'core' database like below

SELECT post_message from posts where 'core'.user_id=123

is that possible?or should I stick with one database?I have also foreign key problem which is related with database relation.

score 1 · Accepted Answer · edited May 23 '17 at 11:46

1

If it is SQL SERVER Product, You can call the Object residing on another DB like below

SELECT * FROM [DB_NAME].[Schema_Name].[Table_NAME]

For Example:

SELECT * FROM DB1.dbo.Employee

For MySQL : Please go through the below ans Select columns across different databases

edited May 23 '17 at 11:46

Community

1
1

answered Dec 10 '16 at 10:24

Shakeer Mirza

5,054
2
18
41

it is mysql,so how can I do this? – Bucky Dec 10 '16 at 10:26
Give it a try. I dont know exact synax in Mysql – Shakeer Mirza Dec 10 '16 at 10:27
Also I use PDO to connect db – Bucky Dec 10 '16 at 10:27
okay but I firstly connect with database via pdo then I use select – Bucky Dec 10 '16 at 10:29
I have tested as you said at comment and it worked! Great! Thank you – Bucky Dec 10 '16 at 10:39

how to select records from multiple database in one query?

1 Answers1