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

Please add your MySQL version

Stu
– Stu

2022-07-19 22:18:31 +00:00
Commented Jul 19, 2022 at 22:18 — Stu
– Stu, Commented Jul 19, 2022 at 22:18
MySQL version 10.5

MareS992
– MareS992

2022-07-19 22:22:22 +00:00
Commented Jul 19, 2022 at 22:22 — MareS992
– MareS992, Commented Jul 19, 2022 at 22:22

NinjaScareCrow · Accepted Answer · 2022-07-20 08:24:28Z

2

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 :)

edited Jul 20, 2022 at 8:24

answered Jul 19, 2022 at 23:21

NinjaScareCrow

1191 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MareS992 Over a year ago

Yes, this is working. Just ON i.news_id need to be ON i.new_id without s on the end. Thank you. Actually, this does not solve my problem, but it works for what I want to try.

IT goldman · Accepted Answer · 2022-07-19 23:32:22Z

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  
    
    ) )

Collectives™ on Stack Overflow

How to limit SQL query with JOIN

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related