SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Oct 2010
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Limit items by category / parent

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    which 10?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2010
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sort by itemid asc, check

    this is untested...
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •