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')->select('emplist', 'b');
$query = db_select('empidmaster', 'a');
$query->join($subquery, 'b', 'b.empid = a.empid');
$query->addField('a', 'empid');
$query->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')->select('emplist', 'b');
$query = db_select('empidmaster', 'a');
$alias = $query->join($subquery, 'b', '%alias.empid = a.empid');
$query->addField('a', 'empid');
$query->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')
->join($subquery, 'b', 'b.empid = a.empid')
->addField('a', 'empid')
->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.