September 12, 2008 at 8:50 am
The following query:
SELECT
vs.[State],
vs.CountyName,
vs.City,
vs.CityID,
vs.CityTypeCode,
vs.ZipCode,
vs.ZipCodeID
FROM dbo.vStateCountyCityZipWithCodes vs
INNER JOIN
(SELECT ZipCode
FROM dbo.vStateCountyCityZipWithCodes
GROUP BY ZipCode
HAVING COUNT(DISTINCT city)>1
)as cs
ON vs.ZipCode = cs.ZipCode
WHERE
vs.ZipCode = 01062
Yields the following result:
State CountyName City CityID CityTypeCode ZipCode ZipCodeID
MA HAMPSHIRE FLORENCE 39974 N 01062 41985
MA HAMPSHIRE FLORENCE 39974 P 01062 41985
MA HAMPSHIRE NORTHAMPTON 40142 N 01062 41985
The results show that there are two different city names for the same zip code. One of those records contains a CityTypeCode of 'p'. What I need is one version of the query to return the record where the CityTypeCode = 'p', and the other version of the query to return the record where the city does not have an instance of 'p'.
The following works for the first query:
SELECT
vs.[State],
vs.CountyName,
vs.City,
vs.CityID,
vs.CityTypeCode,
vs.ZipCode,
vs.ZipCodeID
FROM dbo.vStateCountyCityZipWithCodes vs
INNER JOIN
(SELECT ZipCode
FROM dbo.vStateCountyCityZipWithCodes
GROUP BY ZipCode
HAVING COUNT(DISTINCT city)>1
)as cs
ON vs.ZipCode = cs.ZipCode
WHERE
vs.CityTypeCode = 'p'
AND
vs.ZipCode = 01062
It returns this result:
MA HAMPSHIRE FLORENCE 39974 P 01062 41985
My attempt at the second query is as follows:
SELECT
vs.[State],
vs.CountyName,
vs.City,
vs.CityID,
vs.CityTypeCode,
vs.ZipCode,
vs.ZipCodeID
FROM dbo.vStateCountyCityZipWithCodes vs
INNER JOIN
(SELECT ZipCode
FROM dbo.vStateCountyCityZipWithCodes
GROUP BY ZipCode
HAVING COUNT(DISTINCT city)>1
)as cs
ON vs.ZipCode = cs.ZipCode
WHERE
vs.City NOT IN (SELECT DISTINCT City FROM dbo.vStateCountyCityZipWithCodes WHERE CityTypeCode = 'p')
AND
vs.ZipCode = 01062
the second query should return this:
MA HAMPSHIRE NORTHAMPTON 40142 N 01062 41985
However, the second query does not return any records. What does this query need so that I can get the desired result back?
Thank you for your help.
CSDunn
September 12, 2008 at 8:53 am
Does this help?
WHERE
vs.CityTypeCode <> 'p'
AND
vs.ZipCode = 01062
David
September 12, 2008 at 8:54 am
Ummm why not
SELECT
vs.[State],
vs.CountyName,
vs.City,
vs.CityID,
vs.CityTypeCode,
vs.ZipCode,
vs.ZipCodeID
FROM dbo.vStateCountyCityZipWithCodes vs
INNER JOIN
(SELECT ZipCode
FROM dbo.vStateCountyCityZipWithCodes
GROUP BY ZipCode
HAVING COUNT(DISTINCT city ) > 1) as cs
ON vs.ZipCode = cs.ZipCode
WHERE
vs.CityTypeCode != 'p'
AND
vs.ZipCode = 01062 ?
September 12, 2008 at 9:46 am
Thanks for the responses. Neither of these scripts will work because Florence 'N' comes back with NorthHampton 'N'. Since Florence has a 'P' associated with the ZipCode, then the script should eliminate Florence.
I've tried the following, and it seems to work for at least this particular Zip Code:
SELECT
vs.[State],
vs.CountyName,
vs.City,
vs.CityID,
vs.CityTypeCode,
vs.ZipCode,
vs.ZipCodeID
FROM dbo.vStateCountyCityZipWithCodes vs
WHERE vs.ZipCode = '01062'
AND vs.CityID IN (
SELECT DISTINCT vs.CityID
FROM dbo.vStateCountyCityZipWithCodes vs
WHERE vs.ZipCode = '01062'
GROUP BY vs.City, vs.[State], vs.CityID
HAVING MAX(CASE vs.CityTypeCode WHEN 'P' THEN 1 ELSE 0 END) < 1
)
This will return just the Northampton record for the 01062 zip code.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply