LEFT JOIN and LIMIT only 1 table

[ernest1a]

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 

Do I actually need to use 10 queries?

Thank you

[ScallioXTX]

This should work (not tested) :

SELECT post
     , comment
  FROM (
     SELECT post
       FROM wall_posts
      LIMIT 10
  ) AS wall_posts
  LEFT
  JOIN wall_posts
    ON post_comments.postId=wall_posts.postId

[r937]

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