Post reply

Troublesome query

  • December 17, 2003 at 8:08 am

    #81800

    I would like to get the following or an equivalent query working. Can you suggest anything?

    select a.PostId,a.PostDate,a.PostTitle,Count(b.ReplyId) As ReplyCount,

    (Count(b.ReplyId) WHERE b.ReplyId!=(Select ReplyId from DIARY_SEEN_REPLY WHERE UserId=4))

    FROM DIARYPOST a

    LEFT OUTER JOIN

    DIARYREPLY b on a.PostId=b.PostId group by a.PostId,a.PostDate,a.PostTitle

    It works if you take out this part:

    (Count(b.ReplyId) WHERE b.ReplyId!=(Select ReplyId from DIARY_SEEN_REPLY WHERE UserId=4))

  • December 17, 2003 at 8:29 am

    #486690

    Depending on your data...

    Something like this maybe.... 

    select a.PostId, a.PostDate, a.PostTitle, Count(b.ReplyId) As ReplyCount, 

    Sum( Case When c.ReplyId is NULL Then 0Else 1 End) As SummedIf 

    FROM DIARYPOST a 

    LEFT OUTER JOIN DIARYREPLY b  

    on a.PostId = b.PostId  

    LEFT OUTER JOIN DIARY_SEEN_REPLY C 

    on b.ReplyId!=c.ReplyId 

    and UserId=4 

    group by a.PostId, a.PostDate, a.PostTitle

    You may need to make an aggregate derived table on DIARY_SEEN_REPLY, and then join this instead??

    Hope this helps to get you in the direction you want.



    Once you understand the BITs, all the pieces come together

  • December 17, 2003 at 8:44 am

    #486700

    Thanks. I'll give this a shot.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply