done in a single statement

14 Sep 2006 9:46 PM

Thom Anderson

A table looks like this:
create table #customer ( customerID int not null primary key, taxID char(9)
not null, salesRep int null)
insert #customer values (1, '111111111', 5)
insert #customer values (2, '111111111', null)
insert #customer values (3, '111111112', 7)
insert #customer values (4, '111111113', 6)
insert #customer values (5, '111111111', 5)
insert #customer values (6, '111111113', 9)
insert #customer values (7, '111111113', 7)
insert #customer values (8, '111111114', null)
insert #customer values (9, '111111114', null)
insert #customer values (10, '111111114', null)
insert #customer values (11, '111111115', 4)
insert #customer values (12, '111111115', 4)
insert #customer values (13, '111111115', null)

I want to find all customers where their tax ID is both linked with a sales
rep and not with a sales rep.

In the final query, we should see customers
1, 2, 5, 11, 12, and 13.

We should not see
3 (all times taxID '111111112' is seen there is a rep)
4, 6, and 7 (all times taxID '111111113' appears there is a rep)
8, 9, 10 (all times taxID '111111114' is seen there is never a rep)

I'm looking for a more elegant way to do this than I've tried. Are there any
cool group by or having things that can do this? I've done it with temp
tables which I know isn't very good.

14 Sep 2006 10:01 PM

Chris Lim

Thom Anderson wrote:

> I want to find all customers where their tax ID is both linked with a sales
> rep and not with a sales rep.

Try this:

SELECT c.customerID
FROM    #customer c
WHERE   EXISTS( SELECT *
                FROM    #customer c2
                WHERE   c2.taxid = c.taxid
                AND ( ( c2.salesrep IS NULL AND c.salesrep IS NOT NULL)
                    OR c2.salesrep IS NOT NULL AND c.salesrep IS NULL)
                    )

15 Sep 2006 2:05 AM

Alexander Kuznetsov

Thom Anderson wrote:

Show quote

> A table looks like this:
> create table #customer ( customerID int not null primary key, taxID char(9)
> not null, salesRep int null)
> insert #customer values (1, '111111111', 5)
> insert #customer values (2, '111111111', null)
> insert #customer values (3, '111111112', 7)
> insert #customer values (4, '111111113', 6)
> insert #customer values (5, '111111111', 5)
> insert #customer values (6, '111111113', 9)
> insert #customer values (7, '111111113', 7)
> insert #customer values (8, '111111114', null)
> insert #customer values (9, '111111114', null)
> insert #customer values (10, '111111114', null)
> insert #customer values (11, '111111115', 4)
> insert #customer values (12, '111111115', 4)
> insert #customer values (13, '111111115', null)
>
> I want to find all customers where their tax ID is both linked with a sales
> rep and not with a sales rep.
>
> In the final query, we should see customers
> 1, 2, 5, 11, 12, and 13.
>
> We should not see
> 3 (all times taxID '111111112' is seen there is a rep)
> 4, 6, and 7 (all times taxID '111111113' appears there is a rep)
> 8, 9, 10 (all times taxID '111111114' is seen there is never a rep)
>
>
> I'm looking for a more elegant way to do this than I've tried. Are there any
> cool group by or having things that can do this? I've done it with temp
> tables which I know isn't very good.

Untested:

SELECT taxID
FROM #customer
GROUP BY taxID
HAVING COUNT(salesRep)*COUNT(CASE WHEN salesRep IS NULL THEN 1 END) > 0