CODEWITHSUNDEEP
sqljoincountwhere-clause
andrzej1_1
andrzej1_1

Reputation: 1193

COUNT of joined records in WHERE statement

I have tables like following:

post:

id   status
1    0
2    1
3    1

comment:

id   post_id 
1    2
2    1
3    3
4    2

I want select posts where status=0 or post have comments. I made this query:

SELECT t.*, COUNT(cmt.id) as commentsCount FROM `post` `t` LEFT JOIN comment cmt ON (cmt.post_id = t.id) WHERE t.status='0' OR commentsCount>0 GROUP BY t.id

but it isn't properly.

How to fix this?

P.s There are only simplified tables to make this easier to understand and in my database I can't add field with count.

Upvotes: 0

Views: 117

Answers (3)

greatvovan
greatvovan

Reputation: 3175

There is also more straightforward way to translate your query from English to SQL:

SELECT *
FROM post
WHERE status='0'
  or (select count(1) from comment where post_id = post.id) > 0

I recommend you to check which query has better plan on your data (with join or with subquery). Anyway special field for filtering is highly recommended in your case.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

You need to put this condition in a having clause:

SELECT t.*, COUNT(cmt.id) as commentsCount
FROM `post` `t` LEFT JOIN 
      comment cmt
      ON (cmt.post_id = t.id)
WHERE t.status = '0'
GROUP BY t.id 
HAVING commentsCount > 0;

EDIT:

For the or logic, you can move both conditions to the having clause:

SELECT t.*, COUNT(cmt.id) as commentsCount
FROM `post` `t` LEFT JOIN 
      comment cmt
      ON (cmt.post_id = t.id)
GROUP BY t.id 
HAVING max(t.status) = '0' OR commentsCount > 0;

The max() is, strictly speaking, unnecessary because id is a primary key. But I'm including it for clarity.

Upvotes: 1

Swagata
Swagata

Reputation: 622

In your group by clause you will need all the fields in your select clause apart from the ones with aggregate functions. So your query will be:

SELECT t.id, t.status, COUNT(cmt.id) as commentsCount FROM `post` `t` 
LEFT JOIN comment cmt ON (cmt.post_id = t.id) 
WHERE t.status='0' OR commentsCount>0 GROUP BY t.id, t.status

Also, as status will always be zero, you may probably leave it out from both your select and group by clause.

SELECT t.id, COUNT(cmt.id) as commentsCount FROM `post` `t` 
LEFT JOIN comment cmt ON (cmt.post_id = t.id) 
WHERE t.status='0' OR commentsCount>0 GROUP BY t.id

Upvotes: 0

Related Questions