Count() and left join problem

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 gett开发者_如何学编程ing the shops which have 0 products. How can I achieve this please?

The underlying database is MySQL.

Thanks! Krt_Malta

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

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

You need to add OR p.status IS NULL to your where clause.

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

I suffered this gotcha too and though I am not entirely sure why, placing the predicate on the jojn itself rather than the actual main query is how to solve it.

I actually documented the whole thing, before reading this. I used a simple example with two two small tables, it explains I hope the difference, maybe it will help

http://simpleritsolutions.com/sql/left/join/problems