Reputation: 1193
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
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
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
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