I have 2 mysql tables:
CATEGORY, with fields 'CATID' and 'CATNAME'
ITEMS with fields 'ITEMID', 'CATID' and 'ITEMNAME'
My aim is too select a limited number of items from each category, say 10 by using a single query. There has to be a left join, so the CATNAME field will be included in the result set.
So if there are 5 categories, the query returns a maximum of 50 rows if each category has to display a maximum of 10 items.
Until now i have used two approaches to solve this: either skip the current row if the maximum number of items has been counted for the current category, or retrieve the id from each category, using this in the where clause for a 2nd query.
Both workarounds perform badly with regard to script timing. I'm pretty sure there must be a way to perform it in a single query.