How do i write in codeigniter in active record the following query?
select tbl_list.id, tbl_list_items.name from tbl_list join tbl_list_items on tbl_list_items.id=(select tbl_list_items.id from tbl_list_items where list_id=tbl_list.id order by tbl_list_items.item_no asc LIMIT 1)
Update:
This is what i come out with but it does not do the job.
$this->db->select($this->table_list.'.*');
$this->db->from($this->table_list);
$sub = $this->subquery->start_subquery('join','',$this->table_list_items.'.id');
$sub->select('id')->from($this->table_list_items)->where('list_id','tbl_list.id')->order_by($this->table_list_items.'item_no','asc')->limit('1');
$this->subquery->end_subquery('list_items');
Appreciate any helps. Thanks.