fetch categories/number of items where number of items = 0

[plugged]

Hey,
I'm crafting a SQL query that should return the category names as number of items in the categories (listings.* where listings.category = categories.id). The query works fine when there are items, but when there are 0 items in the category, the category isn't returned.

select categories.name as name
     , count(listings.id) as count
  from categories
     , listings
 where listings.category = categories.id
 group
    by categories.name

Any help?

[JRMillion]

well the join with listings will prevent categories with no listings from being returned...
you may need to do multiple or sub-queries.

you could have a "null" listing for each category and just ignore it.... but that doesnt seem like a great solution....

[r937]

james, allow me to introduce you to the concept of outer joins

select categories.name    as name
     , count(listings.id) as thecount
  from categories
left outer
  join listings
    on categories.id
     = listings.category  
group
    by categories.name