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.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
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_idAll methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0