[SOLVED] Codeigniter, join of two tables with a WHERE clause

Codeigniter, join of two tables with a WHERE clause

I've this code:

public function getAllAccess(){
    $this->db->select('accesscode');
    $this->db->where(array('chain_code' => '123');        
    $this->db->order_by('dateandtime', 'desc');
    $this->db->limit($this->config->item('access_limit'));
    return $this->db->get('accesstable')->result();
}

I need to join it with another table (codenamed table), I've to tell it this. Not really a literal query but what I want to achieve:

SELECT * accesscode, dateandtime FROM access table WHERE chain_code = '123' AND codenames.accselect_lista != 0

So basically accesstable has a column code which is a number, let us say 33, this number is also present in the codenames table; in this last table there is a field accselect_lista.

So I have to select only the accselect_lista != 0 and from there get the corrisponding accesstable rows where codenames are the ones selected in the codenames.

Solution

Looking for this?

SELECT * 
FROM access_table a INNER JOIN codenames c ON
     a.chain_code = c.chain_code
WHERE a.chain_code = '123' AND
      c.accselect_lista != 0

It will bring up all columns from both tables for the specified criteria. The table and column names need to be exact, obviously.