How to use 2 datasources in one query?

Eugene commented on Jan 14

Hi,

how to realize the following query:

SELECT id FROM database1.source_table
WHERE id NOT IN (SELECT transaction_id FROM database2.tracking_table);

database1 and database2 are 2 different datasources

Is it possible to do this in one query or do I have to get an array of transaction_id first to use it in the second query?

Or maybe easier to realize the following:

SELECT s.id 
FROM database1.source_table AS s
LEFT JOIN database2.tracking_table AS t ON s.id = t.transaction_id
WHERE t.transaction_id IS NULL

Sebastian Morales commented on Jan 15

What is your database type and can you join tables from different databases in your sql interface? In your report's datasource setting you can try to set database name to be null and add database name to your sql queries. For example, with pdo mysql:

    'pdoMysql' => [
        'connectionString' => 'mysql:host=127.0.0.1;', // remove "dbname=automaker", do not set dbname here
        'username' => 'root',
        'password' => '',
    ], 
    ...
    $this->src('pdoMysql')
            ->query('select * from automaker.offices') // add dbname before table here

Eugene commented on Jan 15

I use MYSQL

Sebastian Morales commented on Jan 15

In that case you can try the solution in my above post and see if it works or not.

Eugene commented on Jan 15

yes, it works thank you

KoolReport's Forum

How to use 2 datasources in one query? #3229

Build Your Excellent Data Report