MySQL Left Outer Join does not return NULL values ​​for COUNT (*)

Question

I am trying to leave external links in 2 tables: TABLE1 contains a list of users, and TABLE2 contains a list of forms filled in by users. I want to display a form counter by a user created after a given date, where the status is COMPLETED.

The following query works, but does not display NULL values ​​(which I need):

select TABLE1.USERNAME, count(TABLE2.FORMS)
from TABLE1
left outer join TABLE2 on (TABLE2.USERID = TABLE1.USERID)
and TABLE2.STATUS = COMPLETED
where TABLE2.DATE > 20140801
group by TABLE1.USERNAME;

What do I need to do to enable users with a NULL count, i.e. no forms in table 2?

thank

Answer 1

You need to move the condition in the sentence whereto the sentence on:

select TABLE1.USERNAME, count(TABLE2.FORMS)
from TABLE1 left outer join
     TABLE2
     on (TABLE2.USERID = TABLE1.USERID) and TABLE2.STATUS = COMPLETED and
        TABLE2.DATE > 20140801
group by TABLE1.USERNAME;

, , table2 NULL. , , where ( NULL NULL, false).

left outer join on. where.