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

[Lloyd Dupont]

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 Dupont]

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;

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