Help with a query

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.--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

SELECT State, City
FROM table_name
WHERE Primary_Contact<>'y'
GROUP BY State, City

--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUBYA4echKqOuFEgEQLJTACgsOAgruNlQX254w4Abe/ychTn9IAAn11t
O+xXdFBxIeubcPHE0uh6fyoi
=YNNo
--END PGP SIGNATURE--

foneguy2 wrote:

Quote:

Originally Posted by

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.
>

|||On 25 Oct 2006 15:58:36 -0700, foneguy2 wrote:

Quote:

Originally Posted by

>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|||>I have a table with 4 relevant fields [sic] .. <<

Suggestions:
1) learn why a column is nothing like a field
2) Use a numeric code instead of a fake Boolean flag. That way the
MIN() will give you a contact in every location. I would bet you spend
time updating the flags.

CREATE TABLE Contacts
(state_code CHAR(2) NOT NULL,
city_name CHAR(25) NOT NULL,
contact_name CHAR(25) NOT NULL,
contact_priority INTEGER NOT NULL
CHECK (contact_priority 0),
PRIMARY KEY (state_code, city_name, contact_name, contact_priority));

This will show one contact in every city

CREATE VIEW PrimaryContacts (state_code, city_name, contact_name)
AS
SELECT C1.state_code, C1.city_name, C1.contact_name
FROM Contacts AS C1
WHERE contact_priority
= (SELECT MIN (C2.contact_priority)
FROM Contacts AS C2
WHERE C1.state_code = C2.state_code
AND C1.city_name = C2.city_name);