CODEWITHSUNDEEP
mysqljoin
Moon
Moon

Reputation: 22585

Skip counting user's own comment

I thought it would be a simple query, but it's harder than I expected.

I have a simply forum application where users open a story then other users can post comments.

Two tables are

Stories

id
user_id
subject
approved

Comments

id
user_id
story_id
approved

Basically, I would like to query unanswered stories by not counting original poster's own comment. Even if someone else posts a new comment, it should not be considered as an answered story when the comment is not approved yet.

select * from stories left join comments on stories.id = comments.story_id
having count(comments.id) = 0 or ( count(comments.id) > 0 and comments.user_id = stories.user_id )

It works fine without the 'approved' flag, but I just can't make it work when I add 'approved' into where/having clause.

What am I doing wrong?

Upvotes: 1

Views: 32

Answers (2)

Mureinik
Mureinik

Reputation: 311823

I'd move the count into a subquery and put in an in condition to check what's answered and what isn't:

SELECT s.* 
FROM   stories s
WHERE  s.id NOT IN (SELECT   story_id
                    FROM     comments c
                    WHERE    approved = true AND 
                             c.user_id != s.user_id 
                    GROUP BY story_id
                    HAVING   COUNT(*) > 0)

Upvotes: 1

Jens
Jens

Reputation: 69470

Try this untested query:

select * 
from stories 
   left join comments on stories.id = comments.story_id and comments.user_id <> stories.user_id 
having count(comments.id) = 0

Upvotes: 1

Related Questions