How to limit SQL query with JOIN

Question

I have two tables, in one table is news in another is images linked with id from news

news

id	title	main_image	services
1	New title	path_to_image_main_image	Photo
2	New title 2	path_to_image_main_image	Photo

images

id	file_name	new_id
1	IMG_8045.jpg	1
2	IMG_8046.jpg	1
3	IMG_8047.jpg	2
4	IMG_8048.jpg	2
5	IMG_8049.jpg	2

new_id is id from news table

My SQL query is SELECT n.id, n.title, n.main_image, n.services, i.file_name FROM news AS n INNER JOIN images AS i ON n.id = i.new_id I need to limit this query with 2 images from images table per id from news table

Answer 1

By MySQL version 10.5 I assume you mean MariaDB version 10.5... seeing as MySQL is only on version 8.0 at the moment ;)

I'm not too familiar with the syntax differences between MySQL and MariaDB, but here's a query that works in MySQL 8.0... which technically should work for you in MariaDB 10.5 (seeing as they've had window functions since 10.2 - https://mariadb.com/kb/en/window-functions-overview/)

SELECT
    r.*
FROM
(
    SELECT
    n.id,
    n.title,
    n.main_image,
    n.services,
    i.file_name,
    ROW_NUMBER() OVER (PARTITION BY i.new_id ORDER BY i.id) AS row_num
    FROM news n
    INNER JOIN images i ON i.new_id = n.id
) r
WHERE r.row_num <= 2;

Hope this helps :)

Answer 2

I have a solution that supports MySQL 5.6. It is such a mess but it works. I hope performance is not an issue.

Basically it runs the JOIN first time, grouping by id of first table concatenating the ids of second table as a comma separated list. Then do the original join limiting to the list of ids.

Substitute users with news and history with images to name your tables.

SELECT *
FROM 
    `users` AS u
    LEFT JOIN `history` AS h ON u.id = h.user_id 
    
WHERE 
    
FIND_IN_SET(h.id, (SELECT `list` FROM 
(SELECT user_id, SUBSTRING_INDEX(GROUP_CONCAT(id SEPARATOR ','), ',', 3) AS `list` FROM 
(SELECT h.user_id, h.id
FROM 
    `users` AS u
    LEFT JOIN `history` AS h ON u.id = h.user_id
) AS `a`
GROUP BY user_id
) AS `b`
WHERE b.user_id = u.id  
    
    ) )