带计数返回空行的mysql,MySQL-选择COUNT并返回NULL行

This works

SELECT DISTINCT b.b_id

FROM b INNER JOIN c

ON b.b_id=c.b_id

WHERE c.active='yes' AND b.featured='no'

When the results should be 0 rows returned, this returns a null row with count = 0

SELECT DISTINCT b.b_id, COUNT(c.c_id) AS count

FROM b INNER JOIN c

ON b.b_id=c.b_id

WHERE c.active='yes' AND b.featured='no'

Am I doing something wrong?

解决方案

I think you want a left join instead of an inner join since you want to return a count of 0 instead of a missing row when there is no matching c record for a given b record.

Also, you should include a group by when using an aggregate function, such as count.

SELECT

b.b_id,

COUNT(DISTINCT c.c_id) AS count

FROM

b

LEFT JOIN c

ON b.b_id=c.b_id

AND c.active='yes'

WHERE b.featured='no'

GROUP BY b.b_id