sql - COUNT of joined records in WHERE statement -

sql - COUNT of joined records in WHERE statement -

i have tables 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 status=0 or post have comments. made query:

select t.*, count(cmt.id) commentscount `post` `t` left bring together comment cmt on (cmt.post_id = t.id) t.status='0' or commentscount>0 grouping t.id

but isn't properly.

how prepare this?

p.s there simplified tables create easier understand , in database can't add together field count.

you need set status in having clause:

select t.*, count(cmt.id) commentscount `post` `t` left bring together comment cmt on (cmt.post_id = t.id) t.status = '0' grouping t.id having commentscount > 0;

edit:

for or logic, can move both conditions having clause:

select t.*, count(cmt.id) commentscount `post` `t` left bring together comment cmt on (cmt.post_id = t.id) grouping t.id having max(t.status) = '0' or commentscount > 0;

the max() is, strictly speaking, unnecessary because id primary key. i'm including clarity.

sql join count where