select 10 rows joined with all rows from other table

Question

I have 2 tables post and comments and I want to select 10 posts joined with all comments:

 SELECT * from post p left join comments c on p.id = c.post LIMIT 10;

this query will not work since the limit is for post with comments and not for the posts alone. How can I do this then? Is it possible without using inner query?

Answer 1

Try this

SELECT P.*,C.* 
From (SELECT * FROM post LIMIT 0,10) AS P
             Left Join comments C ON C.Id = P.ID

Answer 2

The straight forward solution would be to separate a little:

SELECT * 
FROM comments c 
LEFT JOIN (SELECT * FROM post LIMIT 10) p
  ON p.id = c.post;