How to perform a join with CodeIgniter's Active Record class on a multicolumn key?

Question

I've been able to make this code work using CodeIgniter's db->query as follows:

$sql =
 'SELECT mapping_code,zone_name,installation_name 
  FROM installations,appearances,zones 
  WHERE 
  installations.installation_id = zones.installation_fk_id 
  AND appearances.installation_fk_id =  installations.installation_id 
  AND appearances.zone_fk_id = zones.zone_id 
  AND
  appearances.barcode = ?
 ';

return $this->db->query($sql,array($barcode));

The 'appearances' table throws a 'not unique table' error if I try this using the Active Record class.

I need to join appearances on both the zone and installations tables.

How can I do this?

Answer 1

Take a look at $this->db->join() if you wish to use the ActiveRecord class. Right now you are just using plain SQL which has nothing to do with the ActiveRecord stuff at all.

Does this work when run through a MySQL client like phpMyAdmin, Navicat, etc?