CODEWITHSUNDEEP
sqljoin
user70192
user70192

Reputation: 14214

SQL - LEFT OUTER JOIN and WHERE clause

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.

Upvotes: 5

Views: 23870

Answers (4)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28738

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

Upvotes: 9

JBrooks
JBrooks

Reputation: 10013

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

Upvotes: 3

Noon Silk
Noon Silk

Reputation: 55172

select
  c.name as 'country name'
  isnull(count(p.[ID]), 0) as 'provice count'
from
  Country c
inner join
  Province p on
  p.CountryID = c.[ID]
where
  c.[ID] = @idParameter
  and
  p.[Name] not in ('Washington D.C', 'Another State')

Maybe? Not tested.

-- Edit

Disregard this; as suggested by the poster above it needs a 'group by' to work.

Upvotes: -2

tschaible
tschaible

Reputation: 7695

Can you give this a try?

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

Upvotes: 0

Related Questions