SQL Query Help

[schielrn]

Here is the code that can be copied into sql server to better assist with what I am trying to accomplish. Hope it makes sense and post back with any questions you may have:

create table #temp(period int, location int, custnum varchar(10),
 driver varchar(25), dollar decimal(10,2))

insert into #temp values(201201,221,'ABC123','dwcustcomm',52.37)
insert into #temp values(201201,221,'ABC123','dwfincost',52.37)
insert into #temp values(201202,221,'ABC123','dwcustcomm',52.37)
insert into #temp values(201201,221,'DEF123','dwfincost',52.37)
insert into #temp values(201202,221,'ABC123','dwcustcomm',52.37)
insert into #temp values(201203,221,'ABC123','dwcustcomm',52.37)
insert into #temp values(201201,123,'DEF123','dwcustcomm',52.37)
insert into #temp values(201201,221,'GHI123','dwcustcomm',52.37)
insert into #temp values(201202,221,'XYZ123','dwfincost',52.37)
insert into #temp values(201201,123,'ABC123','dwfincost',52.37)
insert into #temp values(201202,221,'ABC123','dwcustcomm',52.37)
insert into #temp values(201203,221,'XYZ123','dwcustcomm',52.37)

select * from #temp

--Basically what I want is to return the location,custnum combination 
--where only 'dwcustcomm' driver exists for that combination.
--In this sample data above I would expect the following to be returned:
-- 123,DEF123
-- 221,GHI123

--I thought I could do something like this, but it does not work:

select t1.location,t1.custnum
from #temp t1, #temp t2
where t1.period = t2.period and t1.location = t2.location
 and t.custnum = t2.custnum and t1.driver = 'dwcustcomm'
group by t1.location,t1.custnum
having count(t1.driver) = count(t2.driver)

--I do have this work-around to use 2 other temporary tables and I can 
--get my final result, but I would like to get it within the initial select 
--from just one table without having to create other tables.
--create table #tempdwcustcomm(location int, custnum varchar(10), cnt int)

insert #tempdwcustcomm
select location, custnum, count(driver)
from #temp
where driver = 'dwcustcomm'
group by location, custnum

create table #tempall(location int, custnum varchar(10), cnt int)

insert #tempall
select location, custnum, count(driver)
from #temp
group by location, custnum

select c.location, c.custnum
from #tempdwcustcomm c, #tempall a
where c.location = a.location and c.custnum = a.custnum and c.cnt = a.cnt

[BitBlt]

This little query ought to get you what you want:

select t1.location, t1.custnum
from #temp t1
left join #temp t2
on t1.location = t2.location
and t1.custnum = t2.custnum
and t1.driver <> t2.driver
where t1.driver = 'dwcustcomm'
and t2.driver is null

You could always change it to "select distinct blah.blah..." if it gives you duplicate rows in a larger sample set.

Hope that helps! Good luck!

[schielrn]

That is exactly what I was looking for.

Thank you for your assistance.