CODEWITHSUNDEEP
mysqljoin
Mike
Mike

Reputation: 1231

mySQL - Select 2 latest comments for each posts

I trying to limit for only 2 comments for each posts,
i select in the post table and i want to get 2 comments for each

Schema:


Posts table

------------------------------------
  id   |   content   |    date     |
  25    |   hello     |  20/10/2013 |

Comments TABLE

------------------------------------------------
  id   |   content   |    post   |    date     |
  1    |   hello     |    25     |  20/10/2013 |

Could you help me friends, i'm so confused !
Thanks before, Any help will be appreciate.

Upvotes: 4

Views: 1483

Answers (3)

vhadalgi
vhadalgi

Reputation: 7189

MySQL supports the LIMIT keyword, which allows you to control how many rows are returned; ideal when displaying data over many pages. You can use LIMIT in your sql query like this

In your case

select * from posts p join comments c on p.id=c.post and
      c.id> (select id from comments  where post=p.id order by id DESC LIMIT 2,1)

Upvotes: 1

valex
valex

Reputation: 24144

This query returns last 2 comments for each post:

SQLFiddle demo

select p.content post_content,
       c.content comment_content

from posts p
left join comments c on 
    (p.id=c.post)
    and 
    c.id>
    (select id from comments  
        where post=p.id
        order by id DESC LIMIT 2,1)

Upvotes: 1

asantaballa
asantaballa

Reputation: 4048

Syntax may not be perfect, did not have time to create fiddle. But this has subquery which should get the latest 2 comments related to the post and join that to the post itself. Have to consider that fact that there may be no comments at all hence the test for Is Null from the Left Join.

Select *
From Posts p
Left Outer Join Comments c
On c.post = p.id
Where 
    (        c.id Is Null
        Or    c.id In
        (
            Select c2.id
            From Comments c2
            Where c2.post = p.id
            Order by c2.id Desc
            Limit 2
        )
    )

Upvotes: 1

Related Questions