How to GROUP SELECT WITH some value and WITHOUT some other?

I have a table

Customer
===========
INT (Identity), Varchar(50), Varchar(50), Varchar(50)
============
ID, City, FirstName, LastName
=================
1 Brisbane Jon Smith
2 Brisbane Allan Parker
3 Sydney James Parker

And now I try to find
all City
WITH customer named 'Paker'
AND WITHOUT customer named 'Smith'
(That would be 'Sydney' in this sample).

What kind of SQL could I write to do that?


some kind of SQL like below would be good if it could work
SELECT City
FROM Customer
WHERE LastName NOT IN ('Smith') and LastName in ('Parker')
GROUP BY City

Lloyd
Sun Nov 04 05:23:49 PST 2007

I found it!!

==============
SELECT City
FROM Customer
WHERE LastName IN ('Parker')
AND City NOT IN (
SELECT City
FROM Customer
WHERE LastName IN ('Smith')
GROUP BY City )
GROUP BY City;

==============


"Lloyd Dupont" <net.galador@ld> wrote in message
news:ORS20TuHIHA.1316@TK2MSFTNGP02.phx.gbl...
>I have a table
>
> Customer
> ===========
> INT (Identity), Varchar(50), Varchar(50), Varchar(50)
> ============
> ID, City, FirstName, LastName
> =================
> 1 Brisbane Jon Smith
> 2 Brisbane Allan Parker
> 3 Sydney James Parker
>
> And now I try to find
> all City
> WITH customer named 'Paker'
> AND WITHOUT customer named 'Smith'
> (That would be 'Sydney' in this sample).
>
> What kind of SQL could I write to do that?
>
>
> some kind of SQL like below would be good if it could work
> SELECT City
> FROM Customer
> WHERE LastName NOT IN ('Smith') and LastName in ('Parker')
> GROUP BY City
>