views:

356

answers:

1

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?

A: 

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?

Phil Sturgeon
The SQL statement above works perfectly fine -- I was trying to translate it to a series of active record methods such db->join() but all my attempts produce an error concerning 'table not unique' because I have to reference the same table in multiple statements...
Scott Southworth
Can you pastie in your join attempt?
Phil Sturgeon