CODEWITHSUNDEEP
mysqlsql
KunLun
KunLun

Reputation: 3227

Join Tables - include in result rows without data in other table

I have this sql:

SELECT 
    c.id, 
    c.name, 
    SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END)
FROM orders o, customers c
WHERE o.customer_id = c.id
GROUP BY c.id;

Take all customers from table customer.

Count how much rows have column status=1 in table orders, grouped by customer_id.

My sql works perfect if customer have at least one order in table orders(any status, even if all have status different by 1).

But if there is an customer which have no order(does not depend status) in table order, it will not appear in result.

Let's say we have those 2 customers:

100 - C1 - 15 orders(7 have status 1)
101 - C2 - No orders

The Result will be:

100 | C1 | 7

How I can include C2?

100 | C1 | 7
101 | C2 | 0

Upvotes: 1

Views: 188

Answers (2)

Mureinik
Mureinik

Reputation: 312219

You need a left join:

SELECT    id, name, COALESCE(sum_orders, 0)
FROM      customer c
LEFT JOIN (SELECT   customer_id, SUM(CASE WHEN o.status = 1 THEN 1 ELSE 0 END) AS sum_orders
           FROM     orders
           GROUP BY customer_id) o ON c.id = o.customer_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271051

Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

And, you want a LEFT JOIN:

SELECT c.id,  c.name, 
       COUNT(o.customer_id)
FROM customers c LEFT JOIN
     orders o
     ON o.customer_id = c.id AND o.status = 1
GROUP BY c.id;

Upvotes: 2

Related Questions