SQL - LEFT OUTER JOIN and WHERE clause

Question

I'm terrible at SQL. I do not know if what I am trying to do is possible. But, because of our data structure, I need to solve this problem this way or do a massive architectural change.

I am trying to count the number of 'Provinces' (a.k.a States) for a Country. However, there are just a few Provinces that need to be ignored from the count. Because of this, I am trying to retrieve a list of countries, with a count of the provinces in each country.

As an example, I need to query for the United States, and ignore 'Washington D.C.' from the count. The reason why is because by our requirements, Washington D.C. is not a state. Here is what I am trying at the moment (it does not work):

SELECT
  c.Name AS 'CountryName',
  ISNULL(COUNT(p.[ID]), 0) as 'ProvinceCount'
FROM 
  Country c LEFT OUTER JOIN [Province] p ON p.[CountryID]=c.[ID]
WHERE
  c.[ID]=@idParameter and
  p.[Name] <> 'Washington D.C.'

As you can imagine, this query does not return any results when the idParameter matches that of the United States.

How do I get the correct count while figuring in exceptions? Thank you very much for your help.

Answer 1

You need a GROUP BY clause to get a proper count, and you need an outer join to display '0' values for those countries with no valid provinces.

select
  c.Name as 'CountryName',
  isnull(count(c.Name), 0) as 'ProvinceCount'
from
  Country c
left outer join
  Province p on
  p.CountryID = c.[ID]
where
  c.[ID] = @idParameter
  and p.[Name] not in ('Washington D.C', 'Another State')
group by 
  c.Name

Answer 2

You don't want to list out text, a typo can cause a hard to see bug. You also want to eventually make it so the user can have a page to maintain this themselves. So:

ALTER TABLE Province
ADD IsState bit
GO

UPDATE Province
set IsState = 1
where Name not in ('Washington D.C', 'Another State')
GO

UPDATE Province
SET IsState = 0
WHERE IsState IS NULL
GO

-- double check the data at this point by browsing it...

SELECT  c.name AS 'country name',
isnull(count(1), 0) AS 'provice count'
FROM  Country c
INNER JOIN Province p 
ON  p.CountryID = c.[ID]
WHERE c.[ID] = @idParameter
AND p.IsState = 1
GROUP BY c.name
ORDER BY 1  
GO