How to apply a limit just for joining table in MySQL query?

Question

I have two table named category and content (each category can have many contents), and this is my query

SELECT * FROM categories ca 
LEFT JOIN content co ON co.cat_id=ca.id

I want to apply limit on it that query fetch 10 content for each category.

How should I query for this?

Answer 1

There is not rank function on MySQL.

You should use variables. I wrote this snipped for you. Transpose it to your own schema:

MySQL 5.6 Schema Setup:

--c are categories
create table c ( id int, n varchar(100) );

--p are contents
create table p ( id int, fk_c int, n varchar(100) );

insert into c values
(1, 'A'),
(2, 'B');

insert into p values
(1,1,'a'),
(2,1,'b'),
(3,1,'d'),                          <-- id=3, over the limit set to 2
(4,2,'e');

Query:

select * from (
  SELECT p.*, 
         @n := ( case when @c <> c.id then 1 else @n+1 end) as "num",
         @c := c.id as "prev c"
    FROM c left outer join p on c.id = p.fk_c, 
    (SELECT @n := 0, @c = 0)  r
) p  
    where p.num <= 2                <-- limiting to 2.

Results:

| id | fk_c | n | num | prev c |
|----|------|---|-----|--------|
|  1 |    1 | a |   1 |      1 |
|  2 |    1 | b |   2 |      1 |
                                     <-- id=3 missing ;)
|  4 |    2 | e |   1 |      2 |