Limit items by category / parent

Databases
1

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.

2

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.

3

sort by itemid asc, check :slight_smile:

this is untested…


SELECT cat.catid
     , cat.catname
     , itm.itemid
     , itm.itemname
  FROM category AS cat
LEFT OUTER
  JOIN items AS itm
    ON itm.catid = cat.catid
   AND ( SELECT COUNT(*)
           FROM items
          WHERE itemid < itm.itemid ) < 10
4

which 10?