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

4 Nov 2007 1:17 PM

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

4 Nov 2007 1:23 PM

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;

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

Show quote

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