I would like to display on the same page last 10 posts and also all comments for each post. What would be the best way to make this? The problem with LEFT JOIN is it limits 10 posts and comments together.
wall_posts
postId | post
post_comments
commentId | comment | postId
So this is not ok because it can shows only 1 post and 9 comments:
SELECT post,comment FROM wall_posts
LEFT JOIN wall_posts ON post_comments.postId=wall_posts.postId
LIMIT 10
also, some versions of mysql don’t support LIMIT inside a subquery
my advice for this scenario is two queries – one to get the last 10 posts, and the second, using an IN list of post ids from the first query, to pick up all related comments
then the application language can process the two results in tandem