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.
The sort order is not really important, sort by ITEMID asc, could be ok. My itemtable has more fields that i didnt mention, that can serve as a base for sorting order.
sort by itemid asc, check
this is untested...
FROM category AS cat
JOIN items AS itm
ON itm.catid = cat.catid
AND ( SELECT COUNT(*)
WHERE itemid < itm.itemid ) < 10