How do I construct a cross database query in MySQL?

Question

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!

Answer 1

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:

• Identity Qualifiers

Answer 2

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

Answer 3

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.