query 2 databases from one select using active record

[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

[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');

[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

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.

[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();

[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.

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`

[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

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

Much appreciated!