CodeIgniter Forums
Join two tables with two or three same field - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived General Discussion (https://forum.codeigniter.com/forum-21.html)
+--- Thread: Join two tables with two or three same field (/thread-51737.html)



Join two tables with two or three same field - El Forum - 05-15-2012

[eluser]Anil3a[/eluser]
I am newbie so bear with me. I was trying to join two tables having
Code:
gift_id = transact.id, item_id=item_id and user_id=user_id

Should i code like
Code:
$this->db->select('*');
$this->db->from('gift');

$this->db->join('transact', 'transact.id=gift.gift_id,transact.item_id=gift.item_id, transact.user_id=gift.user_id');

$query = $this->db->get();
return $query->result();

And What is really the Left join does ?


Join two tables with two or three same field - El Forum - 05-15-2012

[eluser]Anil3a[/eluser]
Got it .... i just Solved it myself.
Code:
$this->db->select('*');
$this->db->from('gift');
$this->db->join('transact', 'transact.transact= gift.gift_id');
$query = $this->db->get();
return $query->result();

While joining table, the same name of field doesn't matter but must a field should match same to join .... is it.

I think its the answer.


Join two tables with two or three same field - El Forum - 05-17-2012

[eluser]Anil3a[/eluser]
Hello i want to retrieve data from one table comparing with another table. Its like ..... selecting * from TABLE_ONE .... but not the rows that are .... TABLE_ONE_ID = TABLE_TWO_ID.

The TABLE_ONE has ids that are in TABLE_TWO but TABLE_TWO doesn't have ids that are in TABLE_ONE.

I tried this one but did not worked..
Code:
SELECT * FROM gift WHERE gift.gift_id NOT IN (select * from trans where trans_id = $trans_id) ORDER BY gift_id ASC');

where $trans_id is an array of ids that are in trans.

Also tried this one but it does not worked too.
Code:
$this->db->select('*');
$this->db->from('gift');
$this->db->join('trans', 'trans.trans_id = gift.gift_id');
$query = $this->db->where_not_in('gift_id', $trans_ids);



Join two tables with two or three same field - El Forum - 05-17-2012

[eluser]Anak Indonesia[/eluser]
$this->db->select('*');
$this->db->from('gift');
$this->db->join('trans', 'trans.trans_id = gift.gift_id');
$this->db->where_not_in('gift.gift_id', $trans_ids);
$query = $this->db->get();