Count() and left join problem

Question

I'm having a problem with the a query which displays a list of shops with the number of products associated with it. I've been playing around with left joins etc for quite a while now but to no avail. The tables have the following structures:

Shops table containing columns: id, name

Products table containing columns: id, name, status, shop

The query is as follows:

select s.name
       , p.name
       , count(p.id) 
from   Product as p 
       left join Shop as s on p.shop=s.id
where  p.status <> '8796107276379'
group by 
       s.id

I'm not getting the shops which have 0 products. How can I achieve this please?

The underlying database is MySQL.

Thanks! Krt_Malta

Answer 1

You need SHOP on the LEFT side, since the right side is the one that may not have data, in this case PRODUCT.

Not only that, you need the WHERE condition as a LEFT-JOIN ON condition, so that it joins to products on the status condition and just discounts the product (while keeping shop) even if the status is not desired.

select s.name
       , p.name
       , count(p.id) 
from   Shop as s
       left join Product as p on p.shop=s.id AND p.status <> '8796107276379'
group by 
       s.id, p.name

Answer 2

select s.name
       , p.name
       , count(p.id) 
from   Shop as s 
       left join Product as p on s.id=p.shop
where  p.status <> '8796107276379'
group by 
       s.id