N-per-group with JOIN

Question

I am trying to reproduce the logic as shown here . However, I was not lucky when there are associations. The following is a summary version of my query:

SELECT resources.title, catRel.catRef FROM resources LEFT JOIN placesRel ON placesRel.refId = resId LEFT JOIN catRel ON refId = resId WHERE ... 

In short, I get a list containing category identifiers, and I want to limit the results to no more than n results from the category, for example, show only two results on catRef:

 title catRef Swizz Gubbinz 1 Runcible Spoons 1 Peter Pan DVD 2 Button Moon 2 Monkey Alan 3 Bilge Pump 3 

Answer 1

How to use a subquery in your connection. I was not sure that the table refID and resID belonged, but .....

 SELECT resources.title, catRel.catRef FROM resources LEFT JOIN placesRel ON placesRel.refId = resId LEFT JOIN catRel as cr1 ON cr1.catRel.primaryKey in (select cr2.primaryKey from catRel as cr2 where cr2.refID = resId Limit 0,2) WHERE ... 

Answer 2

In the absence of window functions from MySQL, the answer is not trivial. Here is a trick that selects the top N record for each group using MySQL GROUP_CONCAT: MySQL: selecting Top N records per group .

As an aggregate function, GROUP_CONCAT can be manipulated to provide concatenated strings in the desired order. Using text manipulation, the string is parsed. If desired, the values ​​are passed to the appropriate types.