[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