1

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 simple, but I just simply can't get the correct solution.

Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
Fløtti
  • 53
  • 1
  • 4
  • Could you post the query you tried? – Dogbert Feb 10 '11 at 12:47
  • possible duplicate of [mysql: Using LIMIT within GROUP BY to get N results per group?](http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group) – Dan Grossman Feb 10 '11 at 12:48
  • SELECT `papers`.`id` as paper_id,`pictures`.`id` as picture_id, `pictures`.`title` as picture_title,`pictures`.`image` as picture FROM (`papers`) JOIN `pictures` ON `pictures`.`paperid` = `papers`.`id` WHERE `papers`.`pictures` > 0 ORDER BY `papers`.`dato` desc LIMIT 4 – Fløtti Feb 10 '11 at 13:21
  • Here is the limit 4, it limits both the papers and pictures retreived, I'm trying to get 4 papers and every picture associated with the paper. – Fløtti Feb 10 '11 at 13:25

1 Answers1

0

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.

Community
  • 1
  • 1
Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
  • The result does not include the papers result, it is just for limiting the number of papers retreived. Do we still have the same problem? – Fløtti Feb 10 '11 at 13:12