mysql - count null filed of left joint table -

table article id title table comment id articleid comment  select a.*, count(c.id) article left joint comment c  on c.articleid = a.id limit 0, 10 

i want display article number comments, list 1 result (has comment).

and not list articles not have comments.

how list articles (have comments/ have not comment) ?

first of have use group by in base query

select a.id, a.title, count(c.id) comment_count   article left join comment c      on c.articleid = a.id  group a.id, a.title 

sample output:

 | id |  title | comment_count | ------------------------------- |  1 | title1 |             2 | |  2 | title2 |             0 | 

here sqlfiddle demo

now if using left join , want articles comments need apply having clause

select a.id, a.title, count(c.id) comment_count   article left join comment c      on c.articleid = a.id  group a.id, a.title having comment_count > 0 

or use inner join andy suggested because inner join filter out mismatches (meaning articles have no corresponding records in comments table , vice versa). using inner join in cases faster using left join.

select a.id, a.title, count(c.id) comment_count   article join comment c      on c.articleid = a.id  group a.id, a.title 

both produce:

 | id |  title | comment_count | ------------------------------- |  1 | title1 |             2 | 

here sqlfiddle demo