JOIN tables from different databases (same server) - c14

Hi all,
Using Ci4, I have a need to run a query on two tables in different databases (table1 on db1, table2 on db2). I have 2 connections configured & working fine for everything else in //Config/Database (con1, con2).
I need to run a query something like this, where connection (con1) connects to db1:

PHP Code:

public function getEmpsPlusUsers () {
     $this->db = db_connect('con1'); //THIS IS DB1
 
     $sql=("SELECT e.emp_no, e.email, u.email FROM table1 e LEFT JOIN table2 u ON e.email=u.email");
     try {
         $query = $this->db->query($sql);
         $row = $query->getResultArray();
         }catch(\Exception $e) {
         print_r($e);
         die();
     }
 } 


But of couse this throws a error as I do NOT have permission to access db2 becuase I have NOT established a connection!!
Any ideas anyone ???

Set permissions for account1 to access db2.

Set permissions for account1 to access db2.

Thank you, but I'm not really sure how to achieve this!

Would this be done on the database connection?

Many thanks.

You must have an account with the privilege to grant privileges to other users. See the documentation for your DBMS for details.
All manipulations with users are performed through SQL queries.

You must have an account with the privilege to grant privileges to other users. See the documentation for your DBMS for details.
All manipulations with users are performed through SQL queries.

Thank you so much, it works like a dream :-)

The day I spent trying to get this to work wasn't wasted though, as I learnt quite a bit trying all sorts of things in Ci4.

Again, many thanks.

To build a server link, use the sp_addlinkedserver function. For further information, consult the reference documentation. After you've created the server link, you'll write the query normally, merely prefixing the database name with the other server's name. I.E:

Code:

-- FROM DB1
SELECT *
FROM [MyDatabaseOnDB1].[dbo].[MyTable] tab1
    INNER JOIN [DB2].[MyDatabaseOnDB2].[dbo].[MyOtherTable] tab2
        ON tab1.ID = tab2.ID

Once the connection is established, you can use OPENQUERY to execute a SQL query on the remote server and return just the data. This is somewhat quicker, as it allows the remote server to optimize your query. In the example above, if you store the data in a temporary (or in-memory) table on DB1, you'll be able to query it just like joining a conventional database or data independence used in DBMS. For example:

Code:

-- Fetch data from the other database server
SELECT *
INTO #myTempTable
FROM OPENQUERY([DB2], 'SELECT * FROM [MyDatabaseOnDB2].[dbo].[MyOtherTable]')

--
More examples may be found in the OPENQUERY documentation. The above example is somewhat fabricated. In this case, I would absolutely prefer the first technique, however the second alternative utilising OPENQUERY can save some time and speed if you utilise the query to filter out some data.