当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - 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

网友答案:

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')
分享给朋友:
您可能感兴趣的文章:
随机阅读: