Selecting 4 rows per group from two tables

I have 2 tables, which are connected in a one to many relationship. The first table called papers has an id and the other named pictures having paperid as reference to the paperid.

What I'm trying to do is to select all pictures which are associated with each paper. That i can do by building a join, the problem is I can't figure out how to limit the selection of papers, but still want to fetch all the pictures for each paper within the limit.

limit = 4

papers 1: 3 pictures

papers 2: 16 pictures

papers 3: 10 pictures

papers 4: 2 pictures

Hope you understand can help, it should be fairly 开发者_JAVA百科simple, but I just simply can't get the correct solution.

This is the "N per group" problem, and it is not fairly simple. What you want is not a set intersection as most queries are, so you have to do some complicated stuff to force the result. Check out these duplicates of your question:

MySQL Query - recent entries per group

Using LIMIT within GROUP BY to get N results per group?

http://www.artfulsoftware.com/infotree/queries.php#104

There's always the other option of issuing one query per matching paper for its 4 pictures.