query 2 databases from one select using active record

query 2 databases from one select using active record - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: query 2 databases from one select using active record (/showthread.php?tid=26575)

query 2 databases from one select using active record - El Forum - 01-17-2010

[eluser]RJ[/eluser]
Hello,

Does anyone know how I might accomplish the same query as below using CI's active record?

Code:
$q2 = "SELECT a.unique_id, b.NetModemName

FROM nrd_archive.raw_ip_stats_0 AS a, nms.NetModem AS b

WHERE a.unique_id = b.NetModemId

GROUP BY b.NetModemId";

Thanks

query 2 databases from one select using active record - El Forum - 01-17-2010

[eluser]Armchair Samurai[/eluser]
Probably:

Code:
$this->db->select('a.unique_id, b.NetModemName');

$this->db->join('nms.NetModem b', 'a.unique_id = b.NetModemId');

$this->db->group_by('b.NetModemId', 'asc');

$query = $this->db->get('nrd_archive.raw_ip_stats_0 a');

query 2 databases from one select using active record - El Forum - 01-17-2010

[eluser]RJ[/eluser]
Not quite, that produced an error with no output, I adjusted to this:

Code:
$this->db->select('a.unique_id, b.NetModemName');

$this->db->join('nrd_archive.raw_ip_stats_0 AS a', 'nms.NetModem AS b');

$this->db->where('a.unique_id = b.NetModemId');

$this->db->group_by('b.NetModemId', 'asc');

$query = $this->db->get('nrd_archive.raw_ip_stats_0 a');

And receive this

Quote:Error Number: 1066

Not unique table/alias: 'a'

SELECT `a`.`unique_id`, `b`.`NetModemName` FROM (`nrd_archive`.`raw_ip_stats_0` a) JOIN `nrd_archive`.`raw_ip_stats_0` AS a ON `nms`.`NetModem` `AS` b WHERE `a`.`unique_id` = b.NetModemId GROUP BY `b`.`NetModemId`

CI doesn't appear to like aliased tables 'a' or 'b'. I'm not an expert by any means, unless you have any ideas I'll have to stick with the normal query.

query 2 databases from one select using active record - El Forum - 01-18-2010

[eluser]flaky[/eluser]
try this

Code:
$this->db->select('a.unique_id, b.NetModemName');

$this->db->join('nrd_archive.raw_ip_stats_0 AS a', 'nms.NetModem AS b');

$this->db->where('a.unique_id = b.NetModemId');

$this->db->group_by('b.NetModemId', 'asc');

$query = $this->db->get();

query 2 databases from one select using active record - El Forum - 01-18-2010

[eluser]RJ[/eluser]
Closer I think. For some reason there is an extra ` before the AS b. I checked spacing, nothing else I can see to be done on that code to adjust this output.

Quote:You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN `nrd_archive`.`raw_ip_stats_0` AS a ON `nms`.`NetModem` `A

SELECT `a`.`unique_id`, `b`.`NetModemName` JOIN `nrd_archive`.`raw_ip_stats_0` AS a ON `nms`.`NetModem` `AS` b WHERE `a`.`unique_id` = b.NetModemId GROUP BY `b`.`NetModemId`

query 2 databases from one select using active record - El Forum - 01-18-2010

[eluser]davidbehler[/eluser]

Code:
$this->db->select('a.unique_id, b.NetModemName');

$this->db->from('nrd_archive.raw_ip_stats_0 AS a');

$this->db->join('nms.NetModem AS b', 'a.unique_id = b.NetModemId');

$this->db->group_by('b.NetModemId');

Try this

query 2 databases from one select using active record - El Forum - 01-18-2010

[eluser]RJ[/eluser]
Beautiful! Thanks for chiming in, it was bugging me, I knew there must have been a way.

Much appreciated!