CODEWITHSUNDEEP
mysqldatabase
user1774844
user1774844

Reputation: 21

Getting records from multiple databases running on same server?

I am upgrading an application its sort of an ERP based solution, the organization needs some more functionality to easily manage their tasks but the problem im facing is that it contains 20 different set of databases. Let me be more specific.

There are around 20 Databases named as b_d1, b_d2,b_d3 and so on till b_d20. Each database has 2 table which has all user related information eg (d1_g_members and d1_s_members)

so you can say that for b_d1 database there are 2 tables d1_g_members and d1_s_members, similar in this fashion all 20 databases have these tables.

Now coming to my question i want to perform query on all these databases tables with having a where clause meaning :

EXAMPLE:

lets say in my every table there is a field for DATE OF JOINING, and i want to list down all the members according to their date of joining from these 20 dbs. how would i do that ?

I hope i am clarifying my point this far.. ??

Upvotes: 2

Views: 74

Answers (1)

bonCodigo
bonCodigo

Reputation: 14361

Try this as a sample for just joining two databases and the respective tables. Then create the view based on that as the above comment mentioned. Adjust the JOINs according to the data you want to pull out:

SELECT t1.col1, t2.col2 FROM db1.tb1 t1 
JOIN db2.tb2 t2 
ON t1.col1=t2.col1 
WHERE t2.col1 IS NOT NULL
AND t1.DATE_OF_JOINING ? -- your condition for date
;

Upvotes: 1

Related Questions