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

  • Thread starter Thread starter Lloyd Dupont
  • Start date Start date
L

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

==============
 
Back
Top