mysql - How to select all rows even when count() returns nothing?

Question

I have two simple columns for customers and orders. I want to select all customers and the number of appropriate orders even if count is zero (or null or whatever). The following ain't working and returns only customers which have orders:

select customers.id, count(orders.orderid) as total_orders from `xcart_customers` customers 
left join `xcart_orders` orders 
    on customers.id=orders.userid 
where orders.status in ('C', 'K') 
group by customers.id 

Adding having total_orders=0 does not help, because those rows were not selected. Also tried ifnull and many other things (left, right outer join), but no luck.

标签： mysql

Answer 1

这是查询。

select customers.id, count(orders.orderid) as total_orders from `xcart_customers` customers 
left join `xcart_orders` orders 
    on customers.id=orders.userid and orders.status in ('C', 'K') 
group by customers.id