Is it possible to LIMIT results from a JOIN query?

Question

I've got a query that currently queries a Post table while LEFT JOINing a Comment table. It fetches all Posts and their respective Comments. However, I want to limit the number of Comments returned. I tried adding a sub-select, but ran into errors if I didn't LIMIT the results to 1. I'm really not sure how to go about this while still using only one query. Is this possible?

Answer 1

You can't limit a join unless you have some convenient value to filter on (e.g. where pos between 1 and 5). You can select the first comment separately, the second comment, the third comment and so on, and union the results. Something ugly like:

select This, That
from Post
left join (
  select Some
  from Comment
  where PostId = Post.Id
  order by CreatedDate
  limit 1,1
) x on 1=1

union all

select This, That
from Post
inner join (
  select Some
  from Comment
  where PostId = Post.Id
  order by CreatedDate
  limit 2,1
) x on 1=1

union all

select This, That
from Post
inner join (
  select Some
  from Comment
  where PostId = Post.Id
  order by CreatedDate
  limit 3,1
) x on 1=1

Answer 2

This one should get your posts with the three most recent comments per post, assuming that your tables look like that:

post:
id, post_text

comment:
id, post_id, comment_text

SELECT id, post_text, comment_text
FROM
(
    SELECT p.id, p.post_text, c.comment_text
           CASE
             WHEN @id != p.id THEN @row_num := 1
             ELSE @row_num := @row_num + 1
           END AS rank,
           @id := p.id
    FROM post p
    LEFT JOIN comment c ON ( c.post_id = p.id )
    JOIN ( SELECT @id:=NULL, @row_num:=0 ) x
    ORDER BY p.id,
             c.id DESC -- newest comments first
) y
WHERE rank <= 3;

The sub-query is used to get recent comments first and to number them per post, while the outer select removes older comments.