[SOLVED] CodeIgniter active record query with joined tables give error: "Column 'id' in where clause is ambiguous"

CodeIgniter active record query with joined tables give error: "Column 'id' in where clause is ambiguous"

This is my active record script to select data from two tables joined on the same column name:

$this->db->from('d');
$this->db->where('id', $v);
$this->db->join('p', 'p.id = d.id');
$deal=$this->db->get();

This gives a 500 response code and the MySQL error says:

ERROR 1052 (23000): Column 'id' in where clause is ambiguous

The table is:

p(id,home) and d(id, p.id(this value is from p table),school);

How do I fix my active record script?

Solution

in your query

    $this->db->from('d');
    $this->db->where('id',$v);
    $this->db->join('p', 'p.id = d.id'); 
    $deal=$this->db->get();

where caluse is ambigeous, you have to put it like this

  $this->db->where('d.id',$v);

also try

  $this->db->join('p', 'p.id = d.id', 'inner' or 'left');

don't put both just put either inner or left in the join query,hopefully this will solve your problem and also make sure p, d are not synonyms they are actuall name of tables which exists in DB with same p, d names and have valid columns.