Join two tables with two or three same field

[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 ?

[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.

[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);

[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();