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