MySql Query on blogs

My question is how many posts are there with unread comment?

Post Table
------------------

id   title       des开发者_JAVA技巧c
--   -------     ----------

1   hi         hi nari

2  hello     hello nari

3  bye      bye nari



comment table
-----------------

id      Post_id         comment         user_name          read_sts(r- read, n - unread)
----    ------------  --------------  ----------------     ------------
1          1           ggggjhgh             pavan             r

2          2           ghgghggh             naveen            n

3           1         hjhjkhjhhjj           ajay              n

4           1          jkhhjhjhjjj          ajay              n

5           1         ggjghjghg            kalyan             n

6           2         bgjgjkhhhjj           naveen            n

7           1         kjhkjhjhhjk          praveen            r

8           2         mbjhjjhjhjk          ram                r

9           2          gjhgjhghg           ram                n  

My question is how many posts are there with unread comment?

if i know i have 2 posts are there with new comment then i will read those comment and i will market it as read

Try the following:

select count(1) from comment where read_sts ='n' group by post_id

Also, if you wanted to find the title of posts with unread comments you could do:

select id, title from post where id in (select post_id from comment where read_sts ='n')