Search code examples
mysqljoinlimit

LIMIT row with LEFT join

I have 'post' and 'comment' table. I want select last 3 post and all comments for that posts. Curently I use 2 separate statments:

SELECT p.* FROM post p ORDER BY p.date DESC LIMIT 3; // called 1
SELECT c.* FROM comment c WHERE c.post_id = :id; // called 3x time for each post.

It's possible to marge this queries into one?

Solution

  • You can use a subquery for the set of posts:

    SELECT p.*
FROM (SELECT p.*
      FROM post p
      ORDER BY p.date DESC
      LIMIT 3
     ) p JOIN
     comment c
     ON c.post_id = p.id
ORDER BY p.id, c.id;