CODEWITHSUNDEEP
sqlmysql
I-M-JM
I-M-JM

Reputation: 15950

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)

SELECT 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

Upvotes: 1

Views: 126

Answers (2)

Thomas Padron-McCarthy
Thomas Padron-McCarthy

Reputation: 27632

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.

Upvotes: 2

mauretto
mauretto

Reputation: 3212

try:

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

Upvotes: 1

Related Questions