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.
postId | post
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
Do I actually need to use 10 queries?
This should work (not tested) :
) AS wall_posts
LIMIT without ORDER BY makes no sense
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