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.

Simon Verhoeven · Accepted Answer · 2014-01-28 14:58:12Z

9

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

edited Jan 28, 2014 at 14:58

Simon Verhoeven

1,32411 silver badges23 bronze badges

answered Aug 15, 2009 at 14:17

Kirk Broadhurst

28.9k20 gold badges111 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JBrooks · Accepted Answer · 2009-08-15 14:41:17Z

3

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

answered Aug 15, 2009 at 14:41

JBrooks

10k2 gold badges31 silver badges32 bronze badges

Comments

tschaible · Accepted Answer · 2009-08-15 14:14:08Z

0

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

answered Aug 15, 2009 at 14:14

tschaible

7,6951 gold badge33 silver badges35 bronze badges

Comments

Noon Silk · Accepted Answer · 2009-08-15 14:31:34Z

-2

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.

edited Aug 15, 2009 at 14:31

answered Aug 15, 2009 at 14:04

Noon Silk

55.4k6 gold badges91 silver badges105 bronze badges

Collectives™ on Stack Overflow

SQL - LEFT OUTER JOIN and WHERE clause

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related