Return a 0 for a non-join result count?

[Alo]

I have a query that is counting records of a certain type in another table, however, when the count is 0, the join type no longer exists either. How do I get it to return a count of 0 if there is no join? The left join brings me nothing.

for example:

Select p.Name, p.Size, p.Start_Area, p.End_Area, count(pt.Area_type)
from Places p left outer join Place_types pt on p.name_inst_id = pt.name_inst_id
where p.Start_Area = 'SF'
and p.Size = 'Medium'
and pt.Area_type = 'S'
group by p.Name, p.Size, p.Start_Area, p.End_Area

This gives me a number of results, but it doesn't give me a 0 for area type if that Name_inst_id doesn't have an S area type... How do I get it to give me 0 for when that join/count doesn't work?

Thanks.

[DonlonP]

I have a query that is counting records of a certain type in another table, however, when the count is 0, the join type no longer exists either. How do I get it to return a count of 0 if there is no join? The left join brings me nothing.

for example:

Select p.Name, p.Size, p.Start_Area, p.End_Area, count(pt.Area_type)
from Places p left outer join Place_types pt on p.name_inst_id = pt.name_inst_id
where p.Start_Area = 'SF'
and p.Size = 'Medium'
and pt.Area_type = 'S'
group by p.Name, p.Size, p.Start_Area, p.End_Area

This gives me a number of results, but it doesn't give me a 0 for area type if that Name_inst_id doesn't have an S area type... How do I get it to give me 0 for when that join/count doesn't work?

Thanks.

The left join will give you a NULL when no record exists in the other table so to set the count to be 0 when this happens do:
ISNULL(count(pt .Area_type),0)

[wwward]

I'm having the same problem. I want the count of topics for all categories, including zero for no topics. I've tried the ISNULL function, but it still does not return the a row for Category when it has no Topics. This is driving me nuts. Any help is much appreciated.

SELECT
t1.CategoryID,
t1.CategoryName ,
ISNULL(COUNT(t2 .CategoryID),0) AS TopicsCount
FROM
vw_categories AS t1
LEFT OUTER JOIN
Topics AS t2 ON t1.CategoryID = t2.CategoryID
WHERE (t2.DefaultStor yID = 10)
GROUP BY t1.CategoryID, t1.CategoryName