I have two Tables
Table A

Table B

I want to Join table A and Table B in Codeigniter

$this->db->select('Table A.*,Table B.*');
$this->db->where('Table A.id',1);
$this->db->from('Table A');
$this->db->join('Table B','Table A.from=Table B.id');
$this->db->join('Table B as t1','Table A.to=Table B.id');
$orders=$this->db->get()->row();
echo $orders->dname.' - '. $orders->dname;

Upon running above code, it shows Panipat - Panipat while it should show as Panipat - Karnal

CodePudding user response：

You are missing to add the 3rd join of your query the correct way and do not include the column you are interested in to the select clause.

since your code is not using coherent aliases, hence mixing up conventions, you could reduce your example to:

SELECT A.*, B.dname as fromName, C.dname as toName
FROM `table_A` as A
join `table_B` as B on A.from=B.id
join `table_B` as C on A.to=C.id
where A.id=1

this is your corresponding sqlfiddle with the full example

this would be your Codeigniter way to write the query, also taking in account the correct output using the aliases fromName and toName

$this->db->select('A.*, B.dname as fromName, C.dname as toName');
$this->db->where('A.id',1);
$this->db->from('Table A as A');
$this->db->join('Table B as B','A.from=B.id');
$this->db->join('Table B as C','A.to=C.id');
$orders=$this->db->get()->row();
echo $orders->fromName.' - '. $orders->toName;