MYSQL Only limit results from left table, but show all results from right table-CodePudding

I am setting up a database which stores posts for a website called "news". Each news post can have some (0-255) media(s) stored with it. I can get all the data like so:

SELECT * 
FROM news 
LEFT JOIN media ON news.id = news_id

Which returns:

news.id	title	created	media.id	news_id	filename
1	Title1	...	NULL	NULL	NULL
2	Title2	...	NULL	NULL	NULL
3	Title3	...	NULL	NULL	NULL
4	Title4	...	NULL	NULL	NULL
5	Title5	...	1	5	media1.png
5	Title5	...	2	5	media2.png

Notice that news.id = 5 shows up for twice since it has two images associated with it.

My goal is to get the latest 3 posts like so

SELECT * 
FROM news 
LEFT JOIN media ON news.id = news_id 
ORDER BY created DESC 
LIMIT 3

Which returns:

news.id	title	created	media.id	news_id	filename
5	Title5	...	2	5	media2.png
5	Title5	...	1	5	media1.png
4	Title4	...	NULL	NULL	NULL

However I would like it to return all the posts with ids 5, 4, and 3 along with all their media like so:

news.id	title	created	media.id	news_id	filename
5	Title5	...	2	5	media2.png
5	Title5	...	1	5	media1.png
4	Title4	...	NULL	NULL	NULL
3	Title3	...	NULL	NULL	NULL

Is this possible with MySql or is there some other database organization that can accomplish this? Basically I would like to only limit the results from the LEFT table and let the "repeats" show up so I can still get all the RIGHT table data. I am using PHP prepared statements to make these MYSQL queries so I may not be able to use subquery but I am not sure.

CodePudding user response：

Select from a subquery that gets the last 3 posts, rather than the whole table.

SELECT *
FROM (
    SELECT *
    FROM news
    ORDER BY created DESC
    LIMIT 3
) AS news
LEFT JOIN media ON news.id = news_id