|
|
Posted by Hugo Kornelis on 10/26/06 20:55
On 25 Oct 2006 15:58:36 -0700, foneguy2 wrote:
>I have a table with 4 relevant fields (blank lines added for clarity).
>State, City, Name, Primary_Contact
>IL, Springfield, Bill, n
>IL, Springfield, Frank, n
>IL, Springfield, Larry, n
>
>IL, Bloomington, Steve, n
>IL, Bloomington, Chris, y
>
>IL, Chicago, Betty, n
>IL, Chicago, Linda, n
>IL, Chicago, Sue, n
>
>I need a query to return the state and cities that don't have a
>Primary_Contact='y'
>So the results would be:
>IL, Springfield
>IL, Chicago
>
>That's it. Any help is greatly appreciated.
Hi foneguy2,
The solution posted by MGFoster won't work, unfortunately. It will give
you all State/City combo's that have at least one Primary_Contact='n'.
Here's the "straightforward" solution:
SELECT DISTINCT a.State, a.City
FROM YourTable AS a
WHERE NOT EXISTS
(SELECT *
FROM YourTable AS b
WHERE b.State = a.State
AND b.City = a.City
AND b.Primary_Contact = 'n');
And here's a more clever (but harder to graps) solution that might run a
bit faster:
SELECT State, City
FROM YourTable
GROUP BY State, City
HAVING MIN(Primary_Contact) = 'n';
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply).
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|