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

Question

I have two tables named category and content (each category can contain a lot of content) and this is my query

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

I want to apply a restriction to it so that the query displays 10 content for each category.

How do I request this?

Answer 1

There is no rank function in MySQL.

You must use variables. I wrote this cut off for you. Rearrange it in your circuit:

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 |