I want to find all customers that have a certain prlist, but within that same customer grouping that don't contain records for prlist like (cb% or ab%)
In the example provided Customer 123 matches and would be excluded from my output list. Customer 347 has the pli-(prlist), but doesn't have the others like (cb% or ab%) so that's what I want in my output listing.
Thanks.
CREATE TABLE #plist (
ID int PRIMARY KEY IDENTITY(1,1),
custnbr int,
prlist varchar(8)
)
Insert into #plist
(custnbr,prlist)
values(123,'pli-xx4r')
go
Insert into #plist
(custnbr,prlist)
values(123,'ab-xx4r')
go
Insert into #plist
(custnbr,prlist)
values(123,'cb-xx4r')
go
go
Insert into #plist
(custnbr,prlist)
values(347,'pli-xx4r')
Insert into #plist
(custnbr,prlist)
values(347,'cu-xx4r')
Select * from
#plist
October 6, 2022 at 1:57 pm
Sounds like a job for EXISTS (SELECT 1 FROM...) AND NOT EXISTS (...)
October 6, 2022 at 2:03 pm
Tried that but record set didn't prove to be correct..
do you have an example using data, and example output I supplied?
Thx.
Select custnbr
from #plist
group by custnbr
having sum(case when prlist like 'pli%' then 1 else 0 end) = 1 and
sum(case when prlist like '[ac]b%' then 1 else 0 end) = 0
order by custnbr
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 6, 2022 at 2:18 pm
Perfect
Thanks Scott
October 6, 2022 at 2:30 pm
This was removed by the editor as SPAM
October 6, 2022 at 7:52 pm
I know you have an answer, but this may be one of the few cases where EXCEPT will give you what you need. Normally there are additional columns needed which requires joining back to the query to get them (like if you needed the prlist). I copied the clever code from Scott
prlist like '[ac]b%'
Select custnbr
from #plist
where prlist like 'pli%'
except
Select custnbr
from #plist
where prlist like '[ac]b%'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply