title: Codeigniter subquery join
body: <p>How do i write in codeigniter in active record the following query?</p>
  
  <p>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)</p>
  
  <p>Update:</p>
  
  <p>This is what i come out with but it does not do the job.</p>
  
  <pre><code>$this-&gt;db-&gt;select($this-&gt;table_list.'.*');
  $this-&gt;db-&gt;from($this-&gt;table_list);
  $sub = $this-&gt;subquery-&gt;start_subquery('join','',$this-&gt;table_list_items.'.id');
  $sub-&gt;select('id')-&gt;from($this-&gt;table_list_items)-&gt;where('list_id','tbl_list.id')-&gt;order_by($this-&gt;table_list_items.'item_no','asc')-&gt;limit('1');
  $this-&gt;subquery-&gt;end_subquery('list_items');
  </code></pre>
  
  <p>Appreciate any helps. Thanks.</p>