I operate a website where customers can publish products (e.g. pictures). The products are saved inside a MySQL database.

I wish to query going back ten published products However with the constraint of no more than 3 products may come from the single user.

What's the easiest way to do it? My preferred option would be a constraint that's placed on the SQL query asking for the final ten products. But ideas regarding how to setup the database design is extremely welcome.

Thanks ahead of time!

BR

It's pretty easy having a correlated sub-query:

SELECT `img`.`id` , `img`.`userid`
FROM `img`
WHERE 3 > (
SELECT count( * )
FROM `img` AS `img1`
WHERE `img`.`userid` = `img1`.`userid`
AND `img`.`id` > `img1`.`id` )
ORDER BY `img`.`id` DESC
LIMIT 10

The query assumes that bigger id means added later

Correlated sub-queries really are a effective tool! :-)

This really is difficult because MySQL doesn't offer the LIMIT clause on sub-queries. Whether it did, this is rather trivial... But alas, here's my naïve approach:

SELECT
  i.UserId,
  i.ImageId
FROM
  UserSuppliedImages i
WHERE
  /* second valid ImageId */
  ImageId = (
    SELECT MAX(ImageId)
    FROM UserSuppliedImages
    WHERE UserId = i.UserId
  )
  OR
  /* second valid ImageId */
  ImageId = (
    SELECT MAX(ImageId)
    FROM   UserSuppliedImages
    WHERE UserId = i.UserId
      AND ImageId < (
        SELECT MAX(ImageId)
        FROM UserSuppliedImages
        WHERE UserId = i.UserId
      )
    )
  /* you get the picture... 
     the more "per user" images you want, the more complex this will get */
LIMIT 10;

You didn't discuss getting a frequent result order, which means this chooses the most recent images (presuming ImageId is definitely an climbing auto-incrementing value).

To compare, on SQL Server exactly the same would seem like this:

SELECT TOP 10
  img.ImageId,
  img.ImagePath,
  img.UserId
FROM
  UserSuppliedImages img
WHERE
  ImageId IN (
    SELECT TOP 3 ImageId
    FROM UserSuppliedImages 
    WHERE UserId = img.UserId
  )

I'd first choose 10 distinct customers, then choosing images from all of individuals customers having a LIMIT 3, possibly with a union of individuals and limit that to 10.

That will atleast limit the information you have to process to some fair amount.