Answer the question
In order to leave comments, you need to log in
How to set limit on LEFT JOIN?
I'm trying to display for each user from the users table no more than 5 photos from the users_images table. In the code below, no constraint occurs and for each row in the users table, all records from users_images where the id matches are returned.
Also, rn is always equal to one, i.e. it does not increase. Therefore, there is no limit on the number. What could be the problem. Thanks in advance!
SELECT
u.`id`,
u.`name`,
`ui`.`user_id`,
`ui`.`name_image`,
ui.rn
FROM users AS u
LEFT JOIN (SELECT id, user_id, name_image
@row_num := CASE WHEN @row_num_val = user_id THEN @row_num + 1
WHEN (@row_num_val := user_id) IS NOT NULL THEN 1
END rn
FROM users_images, (SELECT @row_num := null, @row_num_val := null) AS x
ORDER BY user_id, id
) AS ui ON ui.user_id= u.id and ui.rn <= 5
Answer the question
In order to leave comments, you need to log in
If your MySQL version allows, see analytic functions.
If you need on 5.7, then you can apply the following magic:
select
case when @userId<>user_id then @q:=1 else @q:[email protected]+1 end q,
case when @q=1 then @userId:=user_id else @userId end user_id
from (select * users_images order by user_id) t1, (select @userId:=0, @q:=1) t2
where user_id<>@userId or ([email protected] and @q<=2)
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question