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.
Advertisement
Answer
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