Execute a Query with join in two tables from different database of same MySQL Server

Question

I need to execute a Query(Select only) with join of two tables from different database of same server.

Eg query will be similar to:

SELECT * FROM DB1.tbl_a LEFT JOIN DB2.tbl_b ON   DB1.tbl_a.fieldX = DB2.tbl_b.fieldY WHERE ....

Where tbl_a,tbl_b are 2 tables from 2 different database DB1,DB2 respectively

How to do that? How can I connect to MySQL server without specifying the database in the connection string but in sql query Using C#.?

What's the problem? Your query should work if you have permissions on both databases — GarethL
– GarethL, Commented Nov 9, 2012 at 10:19
Yes you can do that. Example Query : SELECT a.userID, b.usersFirstName, b.usersLastName FROM databaseA.dbo.TableA a inner join database B.dbo.TableB b ON a.userID=b.userID — Vasanthan
– Vasanthan, Commented Nov 9, 2012 at 10:20

Weacked · Accepted Answer · 2012-11-09 10:25:42Z

1

Actually this question has already been answered, here is the answer :

Yes, assuming the account has appropriate permissions you can use:

SELECT ...
  FROM A.table t1
  JOIN B.table2 t2 ON t2.column = t1.col

You just need to prefix the table reference with the name of the database it resides in.

answered Nov 9, 2012 at 10:20

Weacked

9802 gold badges7 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pawandeep Batra · Accepted Answer · 2012-11-09 10:54:03Z

0

I need to execute a Query(Select only) with join of two tables from different database of same server.

For this SQL User have access on both Databases.

Query will be like this:

SELECT * FROM [DB1].[dbo].[tbl_a] T1 LEFT JOIN [DB2].[dbo].[tbl_b] T2 ON T1.fieldX = T2.fieldY WHERE ....

answered Nov 9, 2012 at 10:54

Pawandeep Batra

12 bronze badges

For this SQL User have access on both Databases. How in C#?

I mean the SQL Server User i.e UserX should have access of both the Databases. so we can access the both databases with the same creds.