how to make this query? 2 tables, one is limited and the other is not

[eluser]DeaD SouL[/eluser]
Hi there..

I have two tables,
categories
pages

so I made this

Code:

$this->db->select("categories.cat_title, categories.cat_cid, categories.cat_pid, categories.cat_news", FALSE);
$this->db->select("pages.page_id, pages.page_title", FALSE);

$this->db->join('pages', "pages.page_cat_id = categories.cat_cid AND pages.page_status = 1", "left");

$this->db->where('categories.cat_active =', 1);

$this->db->order_by('categories.cat_cid', 'ASC');

$query = $this->db->get('categories');

which will generate this

Code:

SELECT
    categories.cat_title,
    categories.cat_cid,
    categories.cat_pid,
    categories.cat_news,
    pages.page_id,
    pages.page_title
FROM
    (categories)
LEFT JOIN
    pages
ON
    pages.page_cat_id = categories.cat_cid
    AND
    pages.page_status = 1
WHERE
    categories.cat_active = 1
ORDER BY
    categories.cat_cid
    ASC

it works just fine..
but it will get all the categories and pages
and i need to get all the categories and just 5 pages for each category..

i think it could be done by sub-select.. i tried.. it just didn't work

any help would be appreciated
thanks

[eluser]DeaD SouL[/eluser]
Any ideas?

Thanks

[eluser]davidbehler[/eluser]
Untested:

Code:

$this->db->select("categories.cat_title, categories.cat_cid, categories.cat_pid, categories.cat_news", FALSE);
$this->db->where('categories.cat_active =', 1);
$this->db->order_by('categories.cat_cid', 'ASC');
$query = $this->db->get('categories');

if($query->num_rows() > 0)
{
  $category_list = $query->result_array();
  foreach($category_list as $key => $value)
  {
    $this->db->select("pages.page_id, pages.page_title", FALSE);
    $this->db->where('page_cat_id', $value['cat_id']);
    $this->db->where('page_status', 1);
    $this->db->limit(5);
    $query = $this->db->get('pages');
    if($query->num_rows() > 0)
    {
      $category_list[$key] = $query->result_array();
    }
    else
    {
      $category_list[$key] = FALSE;
    }
  }
}
else
{
  $category_list = FALSE;
}

return $category_list;

[eluser]DeaD SouL[/eluser]
thanks waldmeister

but do you know how many queries it will make?

i wanted to make it with one query..

anymore ideas?