Problem Joining Tables From Different Databases

Good Morning,

I’m having problems to join data from Different Tables of Different databases of Different DBMSs.

I currently have data in two databases of two different DBMSs: one MySQL, the other SQL server. I have the relationships made in the models and I can use them normally if I use them separately, but when I try to join them, an error occurs because it tries to join in MySQL using a SQL server table.

Is there any way I can join between different DBMS tables?

Example of what I’m trying to do:

class UserCarrier extends \yii\db\ActiveRecord


    //Database: MySQL

    public static function tableName()


        return 'user_carrier';



     * @return \yii\db\ActiveQuery


    public function getCarrier() {

        return $this->hasOne(Carrier::className(), ['A4_COD' => 'carrier_id']);



class Carrier extends \yii\db\ActiveRecord



    //Database: MS SQL server


    public static function tableName()


        return 'SA4010';



And the join I am trying to do (inside the function search() from the UsercarrierSearch model):

class UsercarrierSearch extends Usercarrier


    public function search($params)


        $query = Usercarrier::find();




The error i’m having:

Database Exception – yii\db\Exception

SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘azulejo2.sa4010’ doesn’t exist

The SQL being executed was: SELECT COUNT(*) FROM user_carrier LEFT JOIN user ON user_carrier.user_id = LEFT JOIN SA4010 ON user_carrier.carrier_id = SA4010.A4_COD

Error Info: Array


[0] => 42S02

[1] => 1146

[2] => Table 'azulejo2.sa4010' doesn't exist


Caused by: PDOException

SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘azulejo2.sa4010’ doesn’t exist

in C:\Users\pedro.frota\www\azulejo2\vendor\yiisoft\yii2\db\Command.php at line 902

Note: ‘azulejo2’ is the name of the database that is in MySQL


No, there is no way to do proper join. That’s limitation of these DBs.

Any suggestion? I would like to use this data in GridView, doing search and sort…

Get both into single database in advance.

Only that would solve? Because this data coming from the SQL server is from an ERP system, and unfortunately I cannot do anything other than SELECTs in that database …

Well, alternatively you can do two queries:

  1. SELECT id FROM first_db_table WHERE …;

  2. SELECT * FROM second_db_table WHERE something_id IN (ids from 1st request).

I apologize for the inconvenience, but if you could ask me one more question, I would appreciate it.

I already thought about this case, dividing it into two querys. The same problem is that I need to use this data in an ActiveDataProvider, to search and sort in the "index" view. Would you have any way to do that?

This is new to me, I’ve worked on other projects with Yii, but always using a single Database … I’ve even thought about replicating the SQL server data in MySQL, but this could generate a huge inconsistency …

You can sort when selecting IDs by fields of database A. Then sort like

Search is the same idea but should be easier. Add conditions to corresponding queries.

Ok, thank you! I will try!