How do I construct a cross database query in MySQL?

user_155823 0
Viewed 30612

I've got two databases on the same server. The Google gave me some hints but there wasn't anything "official" that I could find. Could someone point me to the documentation that explains how to do this? An explanation using PHP would be useful as well. Thanks!

mysql sql
3 Answers

I've got two databases on the same server. ...How do I construct a cross database query in MySQL?

You access other databases on the same MySQL instance by prefixing the table with the appropriate database name. IE:

SELECT *
  FROM this_database.table_1 t1
  JOIN that_database.table_2 t2 ON t2.column = t1.column

Keep in mind

A query executes with the credentials of the authentication used to set up the connection. If you want to query two tables simultaneously across two (or more) databases, the user used to run the query will need SELECT access to all databases involved.

Reference:

user_1351520 
SELECT * FROM DB1.myTable1 AS db1, DB2.myTable2 AS db2
user_1786680

http://www.dottedidesign.com/node/14 provides the following example:

SELECT 
  arbogast.node.nid as anid, 
  mcguffin.node.nid as mnid, 
  arbogast.node.title as atitle, 
  mcguffin.node.title as mtitle 
FROM arbogast.node, mcguffin.node 
WHERE arbogast.node.nid = 1 
  AND mcguffin.node.nid = arbogast.node.nid;

Where arbogast and mcguffin are different databases.

user_546800
Related