Union tables in multiple databases

[eluser]Harshadewa[/eluser]
Is it possible in CodeIgniter to connect to multiple databases and include tables from 2 or more databases in one query as below?

SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA

[eluser]danmontgomery[/eluser]

Code:

SELECT E_Name FROM db_a.Employees_Norway
UNION ALL
SELECT E_Name FROM db_b.Employees_USA

Active record doesn't support UNION, so you'd just be using $this->db->query()

[eluser]Harshadewa[/eluser]

Active record doesn’t support UNION, so you’d just be using $this->db->query()

when you say $this->db->query(), its only connecting to 1 database, right?

How can I include 2 databases' tables in one statement like that?

[eluser]Unknown[/eluser]
A UNION is possible, though not directly. Here's a solution I created that uses Active Record but still implements UNION:

$query1 = $this->db->get('Example_Table1');
$join1 = $this->db->last_query();
$query2 = $this->db->get('Example_Table2');
$join2 = $this->db->last_query();
$union_query = $this->db->query($join1.' UNION '.$join2.' ORDER BY column1,column2);

[eluser]danmontgomery[/eluser]
You're executing 3 queries, and the end result is that you still have to use $this->db->query().

[quote author="Harshadewa" date="1285440541"]

Active record doesn’t support UNION, so you’d just be using $this->db->query()

when you say $this->db->query(), its only connecting to 1 database, right?

How can I include 2 databases' tables in one statement like that?[/quote]

Sorry, I never saw this.

You make a connection to a mysql server, not a database. The database is selected after you make the connection. As long as the user you're connecting with has permission in both databases, you can perform a union on tables across databases using the database.tablename method I described above.