mysql left join problem

I have 2 tables

Table: Users
      userid (int, PK)
      name (varchar)
      type (enum)

Table: Sales
      salesid (int)
      productid (int)
      userid (int, FK)

I need output as

 User Name                                  Sales Count
    ABC                                         5
    BCD                                         0
    EFG                                         1

I need all the users from "users" table with specific type (like a,b,c) and sales count (if no sales, then 0)

SELE开发者_如何转开发CT users.name
       , count( sales.salesid ) 
FROM users 
   LEFT JOIN sales 
   ON users.userid = sales.userid 
 WHERE type = 'a'

The problem with above query is, it's only showing me those users that have sales, I need all the users with specific type, whether they have sales or not

Thanks

Aren't you missing the GROUP BY clause? With most SQL dialects you need it, and in the (admittedly rather old) MySQL I'm using, it has to be there, or you get an error. Just add "GROUP BY users.name" at the end, and it should work.

try:

SELECT users.name, count( sales.salesid ) 
FROM users 
   LEFT JOIN sales 
   ON (users.userid = sales.userid AND type = 'a')