Using db_query() or db_select() to connect to multiple databases How can I execute a query that connects to different databases?

I have the following query, and I need to rewrite it for Drupal.

SELECT a.empid, b.empname FROM db1..empidmaster a, db2..emplist b
WHERE a.empid = b.empid


How can I rewrite the query using db_query(), or db_select()?
If the settings.php file defines the information to connect to the other database as $databases['extra']['default'], then you can use the following code, which is possible because SelectQuery::join() accepts also another SelectQuery object as $table argument.

$subquery = Database::getConnection('default', 'extra')-&gt;select('emplist', 'b');
$query = db_select('empidmaster', 'a');
$query-&gt;join($subquery, 'b', 'b.empid = a.empid');
$query-&gt;addField('a', 'empid');
$query-&gt;addField('b', 'empname');


A better code would be similar to the following one. In this case the alias used for the table used for the subquery is the one returned from SelectQuery::join().

$subquery = Database::getConnection('default', 'extra')-&gt;select('emplist', 'b');
$query = db_select('empidmaster', 'a');
$alias = $query-&gt;join($subquery, 'b', '%alias.empid = a.empid');
$query-&gt;addField('a', 'empid');
$query-&gt;addField($alias, 'empname');


SelectQuery::join(), and SelectQuery::addField() don't return the $query object; that is the reason you cannot the following code.

$query = db_select('empidmaster', 'a')
  -&gt;join($subquery, 'b', 'b.empid = a.empid')
  -&gt;addField('a', 'empid')
  -&gt;addField('b', 'empname');


PHP would return an error about a method not being called on an object. In fact, SelectQuery::join() and SelectQuery::addField() return a string: the alias for the table, or the field.