Fetching 10 items each of child table in mysql

Question

I have a table categories (id, cat_name) and a table recipes (id, cat_id, recipe_text).

Now I want to write a query, that fetches from each category 10 recipes.

SELECT cat_name, recipe_text 
FROM categories c 
JOIN recipes r ON c.id=r.cat_id

would fetch ALL recipes, but I want a maximum of 10 recipes per category.

(How) could it be done with a SQL-Query?

Answer 1

Taken from mySQL Returning the top 5 of each category:

SELECT cat_name, recipe_text
FROM
(
   SELECT c.cat_name AS cat_name, r.recipe_text AS recipe_text,
      @r:=case when @g=c.id then @r+1 else 1 end r,
      @g:=c.id
   FROM (select @g:=null,@r:=0) n
   CROSS JOIN categories c
   JOIN recipes r ON c.id = r.cat_id
) X
WHERE r <= 10